selecting most recent record

Question

How to select most recent records if the records are having almost same kind of data...

example:

col1       col2          col3              col4
--------------------------------------------------
123         abc          1.1               12345
123         abc          1.1               123445
1234        dsv          2.0               123
1234        dsv          2.0               1233
12345       dsvw         1.2               1234

The maximum of col4 when it compares with rest of the columns.

See the row1 and row2 data looks similar but we need the latest data based on col4.

Same thing with row3 and row4, data looks similar but we need the latest data based on col4.

And the required output is:

col1    col2   col3   col4
----------------------------
123     abc    1.1    123445
1234    dsv    2.0    1233
12345   dsvw   1.2    1234 

Answer 1

I'm assuming that "latest" means "largest"?

DECLARE @t TABLE
(
    col1 INT, 
    col2 CHAR(3), 
    col3 DECIMAL(4,1), 
    col4 INT
);

INSERT @t SELECT 123, 'abc', 1.1, 12345
UNION SELECT 123, 'abc', 1.1, 123445
UNION SELECT 1234, 'dsv', 2.0, 123
UNION SELECT 1234, 'dsv', 2.0, 1233;

WITH t AS
(
    SELECT col1, col2, col3, col4,
        rn = ROW_NUMBER() OVER
            (PARTITION BY col1 ORDER BY col4 DESC)
        FROM @t
)
SELECT col1, col2, col3, col4
    FROM t
    WHERE rn = 1;