Group By

Hi

I am having problems returning the necessary data with my SQL query. The query below is returning me the correct data but aswell as getting the datemodified column, I need to get the value in the ID column that is associated with the max date modified.

SELECT DISTINCT max([item].[datemodified])
FROM [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]
WHERE [ItemDetails].[ItemTypeID] = 1
GROUP BY [ItemDetails].[ItemValue]

The items table also has an id column, basically I need to get the IDs of the most recent itemvalues. There are multipe entries row in the itemdetail table for the same itemvalue so that why I need to do the group by. In essence I need to order the individual groups, if this is possible.

Any help would be appreciated.

Here it is...

Code Snippet

--On SQL Server 2000

Select

Item.Id

, Item.[datemodified]

From

Item Join

(

SELECT DISTINCT max([item].[datemodified]) max_datemodified

FROM [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]

WHERE [ItemDetails].[ItemTypeID] = 1

) as Data

On Item.[datemodified] = Data.[max_datemodified]

--On SqlServer 2005

Select Id, datemodified

From

(

Select [Item].Id, [item].[datemodified], max([item].[datemodified]) Over (Partition BY 0) Max_datemodified

From [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]

WHERE [ItemDetails].[ItemTypeID] = 1

) as Data

Where

datemodified = Max_datemodified

|||

Thanks for that, just had to change the Partition By 0 to Partition By [ItemDetails].[ItemValue] and it worked perfect.

Cheers.