comparing 2 consecutive rows in SQL

Question

comparing 2 consecutive rows in SQL

rssudden

Mr or Mrs. 500

Points: 512
More actions
August 2, 2008 at 10:47 am

#195176

Hi There
Really need help on this. Write a query that displays pairs of flights, where the destination of the first flight is the departure city for the second flight. E.g. a passenger has to go from city A to B and then on to C. Display the codes of the three cities involved, and the two FlightNums involved.
Please anyone appreciate ur help
Regards
Ed

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 1

If it's only for two flights, then a simple join between destination of first flight and start of second flight for a given passenger number should do it.

If you want a more detailed, tested answer, please provide some data and the structure of the table as outlined in the link in my signature below.

This also sounds a lot like a homework problem... most folks don't mind helping with homework, but show that you've tried...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rssudden Mr or Mrs. 500 Points: 512 More actions · Answer 2

Have tried this query but seems not working

QUERY

------

SELECT distinct R1.FlightNum, R1.FromCityCode, R2.ToCityCode

FROMFlights R1, Flights R2

WHERE(R1.FromCityCode = R2.ToCityCode)

AND(R1.FlightNum < R2.FlightNum)

ORDER BYR1.FlightNum, R1.FromCityCode, R2.ToCityCode

TABLE

------

CREATE TABLE flights

(

flightnum numeric(2) NOT NULL,

fromcitycode character(3) NOT NULL,

tocitycode character(3) NOT NULL,

seatsremaining numeric(3),

aircrafttype character(3)

);

Insert into Flights values (1,'BNE','SYD',10,'AB3');

Insert into Flights values (2,'SYD','CBR',20,'727');

Insert into Flights values (3,'SYD','MEL',30,'757');

Insert into Flights values (4,'SYD','AKL',40,'D10');

Insert into Flights values (5,'BNE','CGK',50,'757');

Insert into Flights values (6,'BNE','LAX',60,'74L');

Insert into Flights values (7,'SYD','HNL',70,'767');

Insert into Flights values (8,'HNL','SFO',80,'767');

Insert into Flights values (9,'SYD','LAX',90,'744');

Insert into Flights values (10,'SYD','BNE',100,'AB3');

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

You're close. your FROM and WHERE clauses are fine.

The problem is that the 2 city codes that you are displaying are the same 2 city codes that you are comparing in your where clause, so 1) you already know that they are the same, and 2) that is the "connecting" city, you probably want to display the starting and ending cities.

If you do that first, then you can just add your connecting city and the FlightNum of the 2nd leg and you will be done.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

rssudden Mr or Mrs. 500 Points: 512 More actions · Answer 4

Thanks Young

I am totally new to SQL and i cant figure out what is wrong. Please if you could post the modify version of the query i've send. Where im doing wrong.

Appreciate ur help and thanks alot mate

Cheers

Ed

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

SELECT distinct R1.FlightNum, R1.ToCityCode, R2.FromCityCode

FROM Flights R1, Flights R2

WHERE (R1.FromCityCode = R2.ToCityCode)

AND (R1.FlightNum < R2.FlightNum)

ORDER BY R1.FlightNum, R1.FromCityCode, R2.ToCityCode

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

rssudden Mr or Mrs. 500 Points: 512 More actions · Answer 6

Mate

Have tried it and it came with an error

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

SQL state: 42P10

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 7

Try your code, Barry. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rssudden Mr or Mrs. 500 Points: 512 More actions · Answer 8

rssudden

Mr or Mrs. 500

Points: 512

August 2, 2008 at 8:00 pm

#852383

My code is not the result that should be

rssudden Mr or Mrs. 500 Points: 512 More actions · Answer 9

Manage to get the right combination

SELECT distinct R1.FlightNum, R1.FromCityCode, R1.ToCityCode

FROM Flights R1, Flights R2

WHERE (R2.FromCityCode = R1.ToCityCode)

AND (R1.FlightNum < R2.FlightNum)

ORDER BY R1.FlightNum

Thanks guys for the help

Keep up the generous work

Ed

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Oops, sorry. Didn't see this until now. Well, glad you worked it out...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]