Help needed in Select Query urgently!!! Please

[neorahav]

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

[hfx642]

Well in MOST seat numbers, the NUMBERS are the Rows, and the LETTERS are the seat (from the left).
However, Rows MAY not be sequential (as in YOUR case).
Since seats beginning with "9" are in the sixth row.
Anyways, what I would do is...

Select * 
from passenger_tbl 
where dyn_flt_alcd = 'FR'
and dyn_bcty = 'BLL'
and LPad (fname_seat1, 3, '0') Like '09%'
and dyn_flt_no = '000'
and dyn_flt_dt = '11/20/2011'
order by LPad (fname_seat1, 3, '0');

I also took the liberty of changing your Where clause to be more efficient.

[neorahav]

Hi Sir,

I have problem now for a scenario

if the seat numbers are
12A
12B
12C
14B
17D
20D
31A
31E
32A
33A

I need to sort from 12B: from the given query if I execute it again gives
12A
12B
12C
14B
17D
20D
31A
31E
32A
33A

But what I need is from 12B :( Please help

Well in MOST seat numbers, the NUMBERS are the Rows, and the LETTERS are the seat (from the left).
However, Rows MAY not be sequential (as in YOUR case).
Since seats beginning with "9" are in the sixth row.
Anyways, what I would do is...

Select * 
from passenger_tbl 
where dyn_flt_alcd = 'FR'
and dyn_bcty = 'BLL'
and LPad (fname_seat1, 3, '0') Like '09%'
and dyn_flt_no = '000'
and dyn_flt_dt = '11/20/2011'
order by LPad (fname_seat1, 3, '0');

I also took the liberty of changing your Where clause to be more efficient.

[urtrivedi]

Kindly explain bit more what exactly you are looking for. because I am not able to understand your case.

Another little long way to do is
I suggest you one thing, If you have control over adding columns to seat master table.
You add one numeric order_no column.
so when you update or insert seat number say 1A, YOU UPDATE ORDER_NO with 165 by some logic, for eg. here I am converting A TO ascii value that is 65.

So whenever you want to sort, join transaction table with seat master table then order by column order_no

[hfx642]

Okay, try...

Select * 
from passenger_tbl 
where dyn_flt_alcd = 'FR'
and dyn_bcty = 'BLL'
and LPad (fname_seat1, 3, '0') >= '12B'
and dyn_flt_no = '000'
and dyn_flt_dt = '11/20/2011'
order by LPad (fname_seat1, 3, '0');