selecting most recent record

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 co开发者_Go百科mpares 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 

---

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;

---

Use:

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

The CTE doesn't provide any optimization over the derived table approach.

---

This does what you want:

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

You may add other columns to the test as required (not clear from question if this is required), 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 is are multiple records that share the same maximum value for col4. This may not be a problem, or you could use DISTINCT or some other treatment as required.

---

I feel like i'm missing something because of all the other answers, but it seems to me 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 would return your desired results