Selecting the most recent entry

Question

How to select the most recent records if the records have almost the same 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 is col4 when it is compared with the rest of the columns.

See the data row1 and row2 look the same, but we need the latest data based on col4.

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

And the required conclusion:

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

Answer 1

Using:

 WITH example AS ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.col1 ORDER BY t.col4 DESC) AS rnk FROM YOUR_TABLE t) SELECT e.* FROM example e WHERE e.rnk = 1 

.. or:

 SELECT e.* FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.col1 ORDER BY t.col4 DESC) AS rnk FROM YOUR_TABLE t) e WHERE e.rnk = 1 

CTE does not provide any kind of derivative optimization.

Answer 2

I assume that "last" 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; 

Answer 3

This does what you want:

 select * from table t where col4 = (select max(col4) from table where col1 = t.col1); 

You can add other columns to the test as needed (unclear from the question, if necessary), for example:

 select * from table t where col4 = (select max(col4) from table where col1 = t.col1 and col2 = t.col2 and col3 = t.col3); 

Note. This will return multiple rows if there are multiple records that have the same maximum value for col4. This may not be a problem, or you can use DISTINCT or some other treatment as needed.

Answer 4

It seems to me that I am missing something because of all the other answers, but it seems to me that this is a simple MAX/group by :

 with temp as ( select 123 as col1,'abc' as col2, '1.1' as col3, 12345 as col4 UNION select 123, 'abc', '1.1' ,123445 UNION select 1234,'dsv','2.0' ,123 UNION select 1234,'dsv' ,'2.0' ,1233 ) SELECT col1, Col2, COl3, MAX(Col4) FROM temp GROUP BY col1, col2,col3 

This will return the desired results.