Help TSQL, The latest records?

How do I find the latest record?
Original data
C_ID M_ID DATE SCORE
-- -- -- --
2467 14843 2005-09-27 45
2467 55877 2005-09-26 89
7392 12365 2005-09-26 98
7392 199128 2005-09-11 78
7412 96143 2005-09-21 68
7412 201850 2005-09-01 86
Desired Result:
C_ID M_ID DATE SCORE
-- -- -- --
2467 14843 2005-09-27 45
7392 12365 2005-09-26 98
7412 96143 2005-09-21 68
DLL:
CREATE TABLE Splat(C_ID INT,
M_ID INT,
[DATE] varchar(10),
[SCORE] tinyint)
INSERT INTO Splat
SELECT 2467, 55877, '2005-09-26' , 89
UNION
SELECT 2467, 14843, '2005-09-27', 45
UNION
SELECT 7392, 12365, '2005-09-26', 98
UNION
SELECT 7392, 199128, '2005-09-11', 78
UNION
SELECT 7412, 96143, '2005-09-21', 68
UNION
SELECT 7412, 201850, '2005-09-01', 86
Thanks,
CulamA common approach:
SELECT *
FROM tbl t1
WHERE t1.date_col = ( SELECT MAX( t2.datecol )
FROM tbl t2
WHERE t2.c_id = t1.c_id );
Make sure you do define keys in your table, if you do not have one.
Anith|||Thank you Sir, you are life-saver!
"Anith Sen" wrote:

> A common approach:
> SELECT *
> FROM tbl t1
> WHERE t1.date_col = ( SELECT MAX( t2.datecol )
> FROM tbl t2
> WHERE t2.c_id = t1.c_id );
> Make sure you do define keys in your table, if you do not have one.
> --
> Anith
>
>