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!!!

this part is useless --
this is the part that's giving you trouble --
why is this "urgent"?

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

I need to deliver the fix in 3 days.. Please help!!!

fix???

actually, "The passengers who have seat number greater than '9A' should be listed" sounds a lot like a homework assignment

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