Help needed in Select Query urgently!!! Please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help needed in Select Query urgently!!! Please

Author

Topic

neoragav
Starting Member

8 Posts

Posted - 2011-12-06 : 04:35:24

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 04:48:59

[code]
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 left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neoragav
Starting Member

8 Posts

Posted - 2011-12-06 : 04:55:15

Sorry sir, I am not getting the seat numbers after '12A'. The result is as below :(

1A
4D
6C
8C
9C
12A
14B
17D
20D
31A
31E
32A
33A

quote:
Originally posted by visakh16

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 left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 05:05:10

that may be because of your other filter conditions.
i've not specifically put anything to filter the ones after 12A. i've added only order by part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neoragav
Starting Member

8 Posts

Posted - 2011-12-06 : 05:07:58

Sorry for not posting my full query :(
This is my query and I am using fname_seat1 >= '12A'

select * from passenger_tbl WHERE fname_alcd='FR' AND fname_flt_no='000' AND fname_flt_dt='12/06/2011' and
len(fname_seat1)>=2 and fname_seat1 >= '12A' order by left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,
stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC

quote:
Originally posted by visakh16

that may be because of your other filter conditions.
i've not specifically put anything to filter the ones after 12A. i've added only order by part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 05:14:27

[code]
select * from passenger_tbl WHERE fname_alcd='FR' AND fname_flt_no='000' AND fname_flt_dt='12/06/2011' and
len(fname_seat1)>=2 and left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 >= 12 and stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') >'A' order by left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,
stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neoragav
Starting Member

8 Posts

Posted - 2011-12-06 : 05:22:39

Perfect sir :) But only one thing is missing. The list now is not including '12A' the list which is generated is
14B
17D
20D
31E

It would be better if the above list also includes '12A'

Thanks in advance.

quote:
Originally posted by visakh16

select * from passenger_tbl WHERE fname_alcd='FR' AND fname_flt_no='000' AND fname_flt_dt='12/06/2011' and
len(fname_seat1)>=2 and left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 >= 12 and stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') >'A' order by left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,
stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neoragav
Starting Member

8 Posts

Posted - 2011-12-06 : 05:54:01

I have following seat numbers
1A
4D
6C
8C
9C
10A
11A
12A
14B
17D
20D
31A
31E
32A
33A

if i use the query given
"select * from passenger_tbl WHERE fname_alcd='FR' AND fname_flt_no='000' AND fname_flt_dt='12/06/2011' and
len(fname_seat1)>=2 and left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 >= 12 and stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') >'A' order by left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,
stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC"

The result is
14B
17D
20D
31E

but the expected result is
14B
17D
20D
31A
31E
32A
33A

Please sir!!!

quote:
Originally posted by neoragav

Perfect sir :) But only one thing is missing. The list now is not including '12A' the list which is generated is
14B
17D
20D
31E

It would be better if the above list also includes '12A'

Thanks in advance.

quote:
Originally posted by visakh16

select * from passenger_tbl WHERE fname_alcd='FR' AND fname_flt_no='000' AND fname_flt_dt='12/06/2011' and
len(fname_seat1)>=2 and left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 >= 12 and stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') >'A' order by left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,
stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 05:56:11

[code]
select * from passenger_tbl WHERE fname_alcd='FR' AND fname_flt_no='000' AND fname_flt_dt='12/06/2011' and
len(fname_seat1)>=2 and left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 >= 12 and stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') >='A' order by left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,
stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neoragav
Starting Member

8 Posts

Posted - 2011-12-06 : 06:00:29

Thank you very much sir!!! It's working like charm:)

quote:
Originally posted by visakh16

select * from passenger_tbl WHERE fname_alcd='FR' AND fname_flt_no='000' AND fname_flt_dt='12/06/2011' and
len(fname_seat1)>=2 and left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 >= 12 and stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') >='A' order by left(fname_seat1,patindex('%[A-Za-z]%',fname_seat1)-1)*1 ASC,
stuff(fname_seat1,1,patindex('%[A-Za-z]%',fname_seat1)-1,'') ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 06:15:05

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neoragav
Starting Member

8 Posts

Posted - 2011-12-08 : 00:38:53

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

quote:
Originally posted by visakh16

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 01:23:51

where will 12A go? you dont want it in resultset at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

neoragav
Starting Member

8 Posts

Posted - 2011-12-08 : 04:16:14

No Sir...
As I want seat numbers after 12B. I wouldn't need 12A to be in result set. I need only seats which would come after 12B

quote:
Originally posted by visakh16

where will 12A go? you dont want it in resultset at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources