| View previous topic :: View next topic |
| Author |
Message |
darapradeep
Beginner
Joined: 12 Feb 2005
Posts: 5
Topics: 2
|
 Posted: Tue Apr 05, 2005 11:28 pm Post subject: Find 4 Consecutive available seat number |
 |
|
hi,
I need a query for the follwig description
I have a table with two attributes namely seatno and
availability as follows
| Code: |
seat avail
101 y
102 n
103 y
104 n
105 y
106 n
107 y
108 y
109 y
110 y
|
from the above table i have to fetch the seatno that is
the availability of the tickets should be continiously 4 yes |
|
| Back to top |
|
 |
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Wed Apr 06, 2005 2:06 pm Post subject: |
|
|
Darapradeep,
So, as per you example should only 107,108, 109,110 be the output records?
Coolman
________
Honda Varadero
Last edited by coolman on Sat Feb 05, 2011 1:42 am; edited 1 time in total |
|
| Back to top |
|
|
darapradeep
Beginner
Joined: 12 Feb 2005
Posts: 5
Topics: 2
|
| Posted: Wed Apr 06, 2005 11:25 pm Post subject: |
|
|
hi coolman,
yes, i want get that output . |
|
| Back to top |
|
|
s_shivaraj
Beginner
Joined: 21 Sep 2004
Posts: 140
Topics: 14
Location: Chennai, India
|
| Posted: Tue Apr 12, 2005 4:49 am Post subject: |
|
|
Darapradeep,
Try this..
But in this query, ur o/p will be only 107 meaning the next 3 seats are available from 107. i.e 107 to 110 are available.
| Code: |
SELECT s.seatno FROM train s where
(s.seatno in (select a.seatno-1 from train a where
(s.seatno in (select p.seatno-2 from train p where
(s.seatno in (select q.seatno-3 from train q where
available='y' ) and available='y')) and available ='y' ))and available ='y');
|
Note : This is based on the assumption that the seat Nos are always in order without any Gaps.
If u need to print all the 4 values then I think the application part can be tuned to print all the values from 107 to 110 from the top value (107, Just print next 3 values).
Gurus,
Correct me if i am wrong,,
Hope the above info helps you.
_________________
Cheers
Sivaraj S
'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity' |
|
| Back to top |
|
|
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Sun Apr 17, 2005 1:38 pm Post subject: |
|
|
Was extremely busy over the last few weeks, didn't get time to look at this...Hence, the delay. This query should give you the desired results..This query would work based on the assumption that your seatno is unique and consecutive.
| Code: |
SELECT A.SEAT, A.FLAG
FROM Emp a
where ('Y' = (SELECT B.FLAG from EMP B where b.SEAT = a.SEAT - 1)
or 'Y' = (SELECT C.FLAG from EMP c where c.SEAT = a.SEAT + 1))
and a.flag = 'Y'
|
________
HALF-BAKED
Last edited by coolman on Sat Mar 12, 2011 8:36 pm; edited 1 time in total |
|
| Back to top |
|
|
s_shivaraj
Beginner
Joined: 21 Sep 2004
Posts: 140
Topics: 14
Location: Chennai, India
|
| Posted: Sun Apr 17, 2005 11:14 pm Post subject: |
|
|
Coolman,
I didn't think so ur solution will work, the above code will display seat Numbers, only when 2 consecutive seats are available.
_________________
Cheers
Sivaraj S
'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity' |
|
| Back to top |
|
|
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Mon Apr 18, 2005 2:16 pm Post subject: |
|
|
Shivraj,
Create a table as per the original poster and run this query and see for yourself. I have tested this query before posting.
Coolman
________
Mazdaspeed6 history
Last edited by coolman on Sat Feb 05, 2011 1:42 am; edited 1 time in total |
|
| Back to top |
|
|
s_shivaraj
Beginner
Joined: 21 Sep 2004
Posts: 140
Topics: 14
Location: Chennai, India
|
| Posted: Mon Apr 18, 2005 11:00 pm Post subject: |
|
|
Coolman,
Could you please explain me the logic behind that..
B'coz when i ran in windows Db2 i am not getting the desired output..and also the code logic doesnt looks fine to me..
I am sorry if i am wrong..
_________________
Cheers
Sivaraj S
'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity' |
|
| Back to top |
|
|
coolman
Intermediate
Joined: 03 Jan 2003
Posts: 283
Topics: 27
Location: US
|
| Posted: Thu Apr 21, 2005 3:55 pm Post subject: |
|
|
Sivaraj,
The logic behind this query is simple. It just takes a seat no, add +/- 1 to it and see if that seat has got a value 'Y'. If yes, it checks once again if the current seat no being processed also has a got a 'Y'. If both the conditions satisfy, the row is pulled out by the query.
Cheers,
Coolman.
PS: btw, the table you tried in Win DB2 did it have any indexes defined on it??
________
vaporizer wiki
Last edited by coolman on Sat Feb 05, 2011 1:42 am; edited 1 time in total |
|
| Back to top |
|
|
s_shivaraj
Beginner
Joined: 21 Sep 2004
Posts: 140
Topics: 14
Location: Chennai, India
|
| Posted: Mon Apr 25, 2005 12:22 am Post subject: |
|
|
Coolman,
| Quote: | It just takes a seat no, add +/- 1 to it and see if that seat has got a value 'Y'
|
So by using the above one, you can only check for 3 consecutive seats and for the below table you will get only 108, 109and 113, the output desired was not that ( It was 107,108, 109,110 )
| Quote: |
seat avail
101 y
102 n
103 y
104 n
105 y
106 n
107 y
108 y
109 y
110 y
111 n
112 y
113 y
114 y
|
Cool man,
You got that ? 
_________________
Cheers
Sivaraj S
'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity' |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12372
Topics: 75
Location: San Jose
|
| Posted: Mon Apr 25, 2005 7:51 am Post subject: |
|
|
Shivraj,
Coolman's query indeed gets the desired results. The result of coolman's query with the data provided by you is
| Code: |
SEAT AVAIL
107 Y
108 Y
109 Y
110 Y
112 Y
113 Y
114 Y
|
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
s_shivaraj
Beginner
Joined: 21 Sep 2004
Posts: 140
Topics: 14
Location: Chennai, India
|
| Posted: Mon Apr 25, 2005 11:27 pm Post subject: |
|
|
Kolusu,
The above is not the desired result.
Darapradeep, wants to Find 4 Consecutive available seat numbers, the above one is not giving that...And also coolman uses 'or' in his query, so it will return the seat nos when current seat and +1/-1 seat is available (  2 consecutive seats ), you can even check the output that you have pasted .
8) Correct me if i am wrong...
_________________
Cheers
Sivaraj S
'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity' |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
