February 8, 2002 at 8:42 am
Now that you dont have much to do heres a little SQL pop quiz that hopefully you can give me some guidance...
I have a temporary table that looks like the following:
Col1 Col2 Col3Col4
A1erterdfgdfgdf
A2erteredfgdfgdf
A3sdfsddfgdfgdf
B1sdfsddfgdfgdf
B2ewtrertdfgdfgdf
B3erterdfgdfgdf
The keys are Col1 + Col2 and I want to extract the Maximum Col2 record for each Col1 row so that my result set looks like:
Col1 Col2 Col3Col4
A3sdfsddfgdfgdf
B3erterdfgdfgdf
There are a number of ways to do this using either temporary or derived tables but I need to find the most efficient way of getting the desired result set out. The table has no indexes and is purely for temp purposes therefore I'm loathe to create indexes unless necessary. The temporary table will also contains millions.
Any ideas on what the best way forward for this?
thanks
Craig
February 8, 2002 at 9:38 am
SELECT mx.COL1, mx.COL2, mn.COL3, mn.COL4 FROM tblNameHere AS mn
INNER JOIN
(SELECT COL1, MAX(COL2) AS MAXCOL2 FROM tblNameHere GROUP BY COL1) AS mx
ON mn.COL1 = mx.COL1 AND mn.COL2 = mx.COL2
This is as tight as you seem to be able to get but it does use a derived table. I will see if I can find another way but cannot see any.
February 8, 2002 at 11:06 am
That would get my vote.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 8, 2002 at 3:23 pm
Two minor changes. There are two areas where mx.COL2 should read mx.MAXCOL2. Don't switch the SELECT clause to mn.COL2, it'll actually increase the cost of the query. Here's the corrected version:
SELECT mx.COL1, mx.MAXCOL2, mn.COL3, mn.COL4 FROM Test AS mn
INNER JOIN
(SELECT COL1, MAX(COL2) AS MAXCOL2 FROM Test GROUP BY COL1) AS mx
ON mn.COL1 = mx.COL1 AND mn.COL2 = mx.MAXCOL2
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 02/08/2002 3:23:51 PM
K. Brian Kelley
@kbriankelley
February 8, 2002 at 4:50 pm
Good catch.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply