SQLTeam.com | Weblogs | Forums

Unique row but multiple columns


#1

Using SQL 2012 and I have a table that looks like this -

Col1   Col2   Col3       Col4     Col5
1      ABC    192.168    Test     01/04/17
2      ABC    192.761    Test2    22/04/17
3      XYZ    142.178     Test8   06/07/17

I am trying to find the unique Col2 (newest based on Date in Col5).

So based on my example above I would expect to see two rows returned -

Col1   Col2   Col3       Col4     Col5
2      ABC    192.761    Test2    22/04/17
3      XYZ    142.178     Test8   06/07/17

I know I can use a distinct and order by but I need the other columns also.

Any help on best to structure the TSQL to achieve what I need.


#2

A common approach is to use ROW_NUMBER:

WITH YourCTE
AS
(
	SELECT Col1, Col2, Col3, Col4, Col5
		,ROW_NUMBER() OVER (PARTITION BY Col2 ORDER BY Col5 DESC) AS rn
	FROM YourTable
)
SELECT Col1, Col2, Col3, Col4, Col5
FROM YourCTE
WHERE rn= 1;

#3

Thank you, that works great and I have learned something new!