#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    3
    Rep Power
    0

    Help needed in Select Query urgently!!! Please


    Hi all,

    I really need help for fixing one of the issue in my project.

    We have a passenger table which is used for Airlines operation. We need to sort the passengers by their seat numbers. Seat numbers are in format <1/2digits><1Alphabet> eg: 1A, 1B, 1C, 22A, 11B, 24D, 9A, 33A, 12B etc...

    I have managed to sort the passenger table by seat number with the following query:

    Select * from passenger_tbl where (dyn_flt_alcd = 'FR') and (dyn_flt_no = '000') and (dyn_flt_dt = '11/20/2011')and dyn_bcty='BLL' order by LEN(fname_seat1),fname_seat1

    where fname_seat1 is varchar(3) which will hold the seat number.

    The problem now is that I have to sort the passengers according to the seat numbers and display only passengers from 6th row. I can get the seat number of 6th row from code, I am unable to form a query which sorts the passengers according to seat numbers from 6th row.

    I have formed the below query which is not working as expected.

    Select * from passenger_tbl where (dyn_flt_alcd = 'FR') and (dyn_flt_no = '000') and (dyn_flt_dt = '11/20/2011')and dyn_bcty='BLL' and len(fname_seat1)>=2 and fname_seat1 >= '9A' order by LEN(fname_seat1),fname_seat1

    where 9A is the seat number of the sixth row. The result is not as expected

    Note: the expected result is that "The passengers who have seat number greater than '9A' should be listed".

    Please help!!!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    this part is useless --
    Code:
    len(fname_seat1)>=2
    this is the part that's giving you trouble --
    Code:
    fname_seat1 >= '9A'
    why is this "urgent"?

    is the plane up in the air and you need this before they land?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    3
    Rep Power
    0

    Nope


    I need to deliver the fix in 3 days.. Please help!!!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    fix???

    actually, "The passengers who have seat number greater than '9A' should be listed" sounds a lot like a homework assignment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    3
    Rep Power
    0

    Is it so easy


    Can you please explain how???
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    Originally Posted by neorahav
    Can you please explain how???
    what you want is to remove the letter, and then treat the rest of it like a number

    so you can use string functions (like SUBSTR or LEFT) to take only the number part, without the letter, and then use CAST to convert the number part to an integer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo