April 25, 2012 at 4:33 am
I have 3 fields. ProductCode,Date, and Value
so for every product code I have multiple dates with values
I need to select the ProductCode and say the Maximum value
So I did something like
Select ProductCode, Max(Value) from TestTable group by ProductCode
But I need the date on which it was Maximum
Can someone suggest a method
April 25, 2012 at 4:56 am
Hi
Its very difficult to help without table structures and test data however you could try something like the following:
SELECT *
FROM
(
ProductCode
,Date
,Value
,ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY Value DESC) AS RowNum
FROM
Product
) AS Prod
WHERE
Prod.RowNum = 1
And if you want the MIN
SELECT *
FROM
(
ProductCode
,Date
,Value
,ROW_nUMBER() OVER (PARTITION BY ProductCode ORDER BY Value ASC) AS RowNum
FROM
Product
) AS Prod
WHERE
Prod.RowNum = 1
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 25, 2012 at 6:31 am
April 25, 2012 at 6:49 am
Hi Andy
Thanks for that. I got it working with your pointer
I still need to read up on this partion thing
April 25, 2012 at 6:57 am
Your welcome 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply