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.
Author |
Topic |
Ranjit.ileni
Posting Yak Master
183 Posts |
Posted - 2011-03-29 : 02:53:26
|
Hi,Here is table DDLcreate table #temp(flightname varchar(20),segmentname varchar(20),depatureplace varchar(20),arrivalplace varchar(20))insert into #temp select 'KL123','kl123a','aaaa','cccc'union allselect 'KL123','kl123b','cccc','dddd'union allselect 'KL123','kl123c','dddd','bbbb'union allselect 'KL126','kl126a','dddd','bbbb'union allselect 'KL126','kl126b','bbbb','cccc'union allselect 'KL128','kl128','aaaa','dddd'select * from #tempflightname segmentname depatureplace arrivalplaceKL123 kl123a aaaa ccccKL123 kl123b cccc ddddKL123 kl123c dddd bbbbKL126 kl126a dddd bbbbKL126 kl126b bbbb ccccKL128 kl128 aaaa ddddHereKL123 flight has 3 segments(aaaa-cccc ,cccc-dddd,dddd-bbbb)KL126 flight has 2 segments(dddd-bbbb ,bbbb-cccc)KL128 has only one segment(aaaa-dddd)segment means Break journeysExpected output:flightname depatureplace arrivalplaceKL123 aaaa bbbbkl126 dddd ccccKL128 aaaa ddddPlease help me how to form a Query--Ranjit |
|
Devart
Posting Yak Master
102 Posts |
Posted - 2011-03-29 : 03:20:56
|
Hello.Try this:SELECT flightname, depatureplace, (SELECT TOP 1 arrivalplace FROM #temp WHERE flightname=t.flightname ORDER BY segmentname DESC) AS arrivalplaceFROM( SELECT row_number()OVER (PARTITION BY flightname ORDER BY segmentname) AS rn, flightname,depatureplace FROM #temp) tWHERE rn=1Devart,SQL Server Tools:dbForge Data StudiodbForge Schema ComparedbForge Data ComparedbForge SQL Complete |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-03-30 : 18:16:07
|
[code]select frst.flightName, frst.departurePlace, lst.arrivalPlacefrom #temp frst --this is first/starting segmentjoin #temp lst on frst.flightName = lst.flightName --this is last/ending segmentwhere --there is no previous segment for the first one not exists(select * from #temp prv where frst.flightName = prv.flightName and prv.arrivalPlace = frst.departurePlace)--there is no next segment for the last one and not exists(select * from #temp nxt where lst.flightName = nxt.flightName and lst.departurePlace = nxt.arivalPlace)[/code]MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
Ranjit.ileni
Posting Yak Master
183 Posts |
Posted - 2011-03-31 : 02:14:05
|
Hi mmarovic,your Query Results not matching with my Expected Output.KL123 aaaa cccc --bbbbKL126 dddd bbbb --ccccKL128 aaaa dddd--Ranjit |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-03-31 : 02:42:37
|
quote: Originally posted by Ranjit.ileni Hi mmarovic,your Query Results not matching with my Expected Output.KL123 aaaa cccc --bbbbKL126 dddd bbbb --ccccKL128 aaaa dddd--Ranjit
My bad, there was a mistake in the query. You should be able to fix it yourself if you understood comments:select frst.flightName, frst.departurePlace, lst.arrivalPlacefrom #temp frst --this is first/starting segmentjoin #temp lst on frst.flightName = lst.flightName --this is last/ending segmentwhere --there is no previous segment for the first one not exists(select * from #temp prv where frst.flightName = prv.flightName and prv.arrivalPlace = frst.departurePlace)--there is no next segment for the last one and not exists(select * from #temp nxt where lst.flightName = nxt.flightName and nxt.departurePlace = lst.arivalPlace) MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
Ranjit.ileni
Posting Yak Master
183 Posts |
Posted - 2011-03-31 : 02:50:05
|
I fix the problem by changing join condition thank you very much.--Ranjit |
|
|
|
|
|
|
|