December 6th, 2011, 03:33 AM
Help needed in Select Query urgently!!! Please
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".
December 6th, 2011, 04:20 AM
this part is useless --
this is the part that's giving you trouble --
why is this "urgent"?
fname_seat1 >= '9A'
is the plane up in the air and you need this before they land?
December 6th, 2011, 04:28 AM
I need to deliver the fix in 3 days.. Please help!!!
December 6th, 2011, 05:10 AM
actually, "The passengers who have seat number greater than '9A' should be listed" sounds a lot like a homework assignment
December 6th, 2011, 05:27 AM
Is it so easy
Can you please explain how???
December 6th, 2011, 07:56 AM
what you want is to remove the letter, and then treat the rest of it like a number
Originally Posted by neorahav
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