October 25, 2007 at 4:22 am
I'm looking for an efficient solution to returning a field in a row, where another field in the row is the MAX in a grouping.
Say I have the following table #FindIDForMaxValue:
[font="Courier New"]
UniqueID NonUniqueID AValue
----------- ----------- -----------
1 A 10
2 A 2
3 A 5
4 B 4
5 B 8
6 C 1
[/font]
I want to find the UniqueID for the row that has the largest AValue for each NonUniqueID.
If I assume that AValue is unique for each NonUniqueID, I could do the following:
[font="Courier New"]
SELECT
allvalues.*
FROM
#FindIDForMaxValue allvalues
INNER JOIN
(
SELECTMAX(AVaLue) As topvalue
FROM#FindIDForMaxValue
GROUP BY NonUniqueID
) topvalues
ON allvalues.aValue = topvalues.topvalue
[/font]
In reality the query for #FindIDForMaxValue is large and also dynamically generated. My questions are as follows:
- am I right in thinking that if #FindIDForMaxValue is actually a dynamicallygenerated SQL query, then simply putting it into the subquery as well would be extremely inefficient?
-would I be better off putting the initial query into a temporary table, and call the subquery as above?
- is there an aggregate function that returns a reference to the row that matches an aggregate function such as MAX?
Many thanks,
Ali
Table generation script:
IF OBJECT_ID( 'tempdb..#FindIDForMaxValue' ) IS NOT NULL
DROP TABLE #FindIDForMaxValue
CREATE TABLE #FindIDForMaxValue( UniqueID int NOT NULL, NonUniqueID VARCHAR(10) NOT NULL, AValue INT NULL )
INSERT #FindIDForMaxValue(UniqueID,NonUniqueID,AValue)
SELECT 1,'A',10
UNION
SELECT 2,'A',2
UNION
SELECT 3,'A',5
UNION
SELECT 4,'B',4
UNION
SELECT 5,'B',8
UNION
SELECT 6,'C',1
SELECT * FROM #FindIDForMaxValue
IF OBJECT_ID( 'tempdb..#FindIDForMaxValue' ) IS NOT NULL
DROP TABLE #FindIDForMaxValue
[/font]
October 25, 2007 at 4:38 am
I've been thinking more about this - what if AValue is NULL or not-unique between differing values of NonUniqueID? Then I would be joining up on totally incorrect rows. My SQL query above is not correct. How on earth do I sort this out - I can only think to have a function that looks at each grouping of NonUniqueId and returns a TOP 1 from the ORDERed rowset. But isn't this also really ineffecient?
October 25, 2007 at 4:42 am
I've also just realised that I'm working in SQL Server 2005 for this database, not SQL server 2000 (which I normally have to work in). Dang - this should be in the other forum. Any way of moving it?
Would be interested in the SQL 2000 solution if it's different, so it may be pertinent to leave it here as well.
Sorry!
October 25, 2007 at 4:51 am
DECLARE@Sample TABLE (UniqueID INT, NonUniqueID VARCHAR(10), aValue INT)
INSERT@Sample
SELECT1, 'A', 10 UNION ALL
SELECT2, 'A', 2 UNION ALL
SELECT3, 'A', 5 UNION ALL
SELECT4, 'B', 4 UNION ALL
SELECT5, 'B', 8 UNION ALL
SELECT6, 'C', 1
SELECTUniqueID,
NonUniqueID,
aValue
FROM(
SELECTUniqueID,
NonUniqueID,
aValue,
ROW_NUMBER() OVER (PARTITION BY NonUniqueID ORDER BY aValue DESC) AS RecID
FROM@Sample
) AS d
WHERERecID = 1
N 56°04'39.16"
E 12°55'05.25"
October 25, 2007 at 4:56 am
- am I right in thinking that if #FindIDForMaxValue is actually a dynamicallygenerated SQL query, then simply putting it into the subquery as well would be extremely inefficient?
The query which you've posted is not a sub query instead it is termed as a derived table...
-would I be better off putting the initial query into a temporary table, and call the subquery as above?
Yes, its always better working a smaller a set of data:)
- is there an aggregate function that returns a reference to the row that matches an aggregate function such as MAX?
In SQL 2000/2005
SELECT
allvalues.*
FROM
#FindIDForMaxValue allvalues
INNER JOIN
(
SELECT NonUniqueID, MAX(AVaLue) As topvalue
FROM #FindIDForMaxValue
GROUP BY NonUniqueID
) topvalues
ON allvalues.NonUniqueID = topvalues.NonUniqueID AND allvalues.aValue = topvalues.topvalue
In SQL 2005
SELECTT.NonUniqueID, T.AVaLue
FROM(
SELECTROW_NUMBER() OVER( PARTITION BY NonUniqueID ORDER BY NonUniqueID, AValue DESC ) AS RowNum,
*
FROM#FindIDForMaxValue
) T
WHERE T.RowNum = 1
EDIT
Peter,
Your fingers are quite faster on keyboard than me...:):):)
--Ramesh
October 25, 2007 at 6:22 am
Peter and Ramesh - thank you so much for such prompt replies, the SQL gods are obviously smiling on me today! 😀 I'm fairly new to SQL 2005 so I will look at OVER and PARTITION.
Ramesh - on the SQL 2000 answer - my brain is obviously broken today, I should've known that.
Many, many thanks again!
Ali
October 25, 2007 at 6:40 am
Alergy (10/25/2007)
Peter and Ramesh - thank you so much for such prompt replies, the SQL gods are obviously smiling on me today! 😀 I'm fairly new to SQL 2005 so I will look at OVER and PARTITION.Ramesh - on the SQL 2000 answer - my brain is obviously broken today, I should've known that.
Many, many thanks again!
Ali
I glad:):) that solved your problem...although it was fairly simple:hehe:
--Ramesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply