0

looking to get some help running a .sql query in Microsoft SQL Server Management...

hoping to filter out older results from a table similar to the following:

Marker Version Value
1 001 A
1 002 B
2 001 X
2 002 Y
2 003 Z
3 001 D
3 001 E
3 001 F

I need to filter out older versions of Marker, to keep all values for the unique marker's most recent version such that:

Marker Version Value
1 002 B
2 003 Z
3 001 D
3 001 E
3 001 F
Nsheetz
  • 1
  • Can you share your best coding attempt at this problem? – lemon Oct 13 '22 at 14:57
  • 2
    Seems like you want [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group), but replace `ROW_NUMBER` with `DENSE_RANK`. – Thom A Oct 13 '22 at 15:01

1 Answers1

2

Try something like this:

--Create an extra column to identify the most recent Version for each Marker.
WITH prelim AS (
    SELECT *, DENSE_RANK() OVER(PARTITION BY Marker ORDER BY [Version] DESC) as row_num
    FROM tablename
)
SELECT *
FROM prelim
WHERE row_num = 1  --Filters out older results
Tim Jarosz
  • 1,133
  • 1
  • 8
  • 15