July 25, 2008 at 12:47 pm
I have a working cursor-based query for returning the unique results of a listing of 'from' (point A) and 'to' (point b) destination cities in a 'Routes' table. Unique in terms of in the routes table there can be rows with be the same cities in either the point A or point B columns, i.e. one row with point A = 'LA' and point B = 'NY' and another row with point A = 'NY' and point B = 'LA' but we only want to show the first pair and not 'duplicate' row with the pair in the reversed situation.
So given a table with the sample data:
DECLARE@Routestable
(
PointA char(2) NOT NULL,
PointB char(2) NOT NULL
)
INSERT INTO @Routes (PointA, PointB)
SELECT 'LA', 'NY'
UNION SELECT 'KC', 'NY'
UNION SELECT 'BO', 'KC'
UNION SELECT 'NY', 'LA'
I need to have a result set of the 'unique' point A & B pairs as follows (sorted by PointA, PointB):
PointA PointB
------ ------
BO KC
KC NY
LA NY
Here is my cursor-based solution:
DECLARE@Routes_Uniquetable
(
PointA char(2) NOT NULL,
PointB char(2) NOT NULL
)
DECLARE@PointA char(2),
@PointB char(2)
DECLARE route_cursor CURSOR FOR
SELECT PointA, PointB FROM @Routes
OPEN route_cursor
FETCH NEXT FROM route_cursor INTO @PointA, @PointB
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT 1 FROM @Routes_Unique WHERE PointB = @PointA AND PointA = @PointB)
INSERT INTO @Routes_Unique SELECT @PointA, @PointB
FETCH NEXT FROM route_cursor INTO @PointA, @PointB
END
CLOSE route_cursor
DEALLOCATE route_cursor
SELECT * FROM @Routes_Unique ORDER BY 1, 2
I would like to see if it is possible to achieve the same results for this seemingly simple task with a set-based approach and as a bonus eliminate the temp table. My first attempt was as follows but of course doesn't work as there the 'unique' table does not have any rows in it to compare existence against until the operation is over and is not adding them incrementally as the cursor solution is.
INSERT INTO @Routes_Unique
SELECT r1.PointA, r1.PointB
FROM @Routes r1
WHERE NOT EXISTS (SELECT 1 FROM @Routes_Unique r2
WHERE (r2.PointA = r1.PointA AND r2.PointB = r1.PointB)
OR (r2.PointA = r1.PointB AND r2.PointB = r1.PointA))
SELECT * FROM @Routes_Unique ORDER BY 1, 2
From my first attempt I've strung together a godawful number of convoluted joins and nested joins and still can't get what I'm after and now it all looks like a dog's breakfast. I'm sure this could be turned into a WHILE loop to do the same thing but that really wouldn't accomplish my goal as it is still a hidden RBAR. No amount of caffiene I've ingested this morning has allowed me a solution so I'm looking to you folks for another set of eyeballs and brains.
Best Regards
maddog
July 25, 2008 at 12:59 pm
In SQL 2005, this would be dead-easy, with the row_number() and except commands. In 2000, the only thing I can think of requires dumping the data into temp tables with ID numbers, and eliminating odd-numbered IDs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2008 at 1:08 pm
Thanks for the response. Yeah I tried flattening out the A&B values into another temp table with an ID, an A\B type, and single column for the values but I got even more confused trying to retain the original relationships to the paired rows.
Regards,
maddog
July 25, 2008 at 3:59 pm
This should do it:
Select PointA, PointB
From @Routes
Where PointA <= PointB
UNION
Select PointB, PointA
From @Routes
Where PointA > PointB
[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]
July 25, 2008 at 4:02 pm
If you need the reversed rows back in thier original column order, that is slightly more complex (which I will leave as a problem for the reader). 🙂
[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]
July 25, 2008 at 4:34 pm
Well yahoo - that works for me Barry. I had tried various forms of unions also but didn't think of using the equivalence operators like you did. Thanks much for this solution!
Cheers,
maddog
July 25, 2008 at 5:58 pm
Glad I could help.
[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]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply