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".