I need a query that can return all the occurrence of specific condition pairs in a table. for example, I have this table. I need to return all the "start to stop" leg where the startPointType is either L or D and the endPointType is not L or D.
| leg | StartPoint | StartPointType | EndPoint | EndPointType |
| ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | xxx | L | aaa | D |
| 2 | aaa | L | eee | Z |
| 3 | eee | Z | ttt | C |
| 4 | ttt | C | uuu | D |
| 5 | uuu | L | ooo | L |
| 6 | ooo | L | hhh | C |
| 7 | hhh | C | kkk | B |
| 8 | kkk | B | ppp | C |
| 9 | ppp | C | fff | L |
| 10 | fff | L | www | L |
In the example above, it should return
| Start_Leg | StartPoint | End_Leg | EndPoint |
| ------------ | ------------ | ------------ | ------------ |
| 2 | aaa | 4 | uuu |
| 6 | ooo | 9 | fff |
I'm able to find the first pair, but can't figure how to get all the pairs.. Many thanks for your help.
I tried self join.
I'm expecting to get all the pairs.
1条答案
按热度按时间fdbelqdn1#
Considering the desired output in the post I think the request should be:
If I am not misunderstanding, the following query gives the desired result:
where MyTable is a table like the one in the post. I'm joining it with itself and where the alias is "a" I'm looking for start records and where is "b" I'm looking for stop records: this is the explanation why I wrote a.leg<b.leg in the join condition; the remaining part of the join condition is point (2) above, whereas where condition is point (1).
The reason why I wrote select top 1 with ties and that order by at the end is because I want to have only one record for each partition key, a leg in our case. In case of many records with the same a.leg I want to keep only the one with the smaller b.leg (that's why in the row_number() i orber by b.leg).
This solution could not work in sql server 2008, in that case I would use CTE (or temp tables as you prefer):