Thursday, July 20, 2006

Sql T-Sql Sql server 2000 select or show distinct rows by column instead of the entire row. In this case it only shows the latest by date. 2 examples are provided. Thanks to Steve Hamilton.


/*
CREATE TABLE #TestTable( ID INT, TEST VARCHAR(50), TEST2 VARCHAR(50), TEST3 DATETIME )

INSERT INTO #TestTable VALUES ( 1, 'A', 'A', GETDATE() + 0 )
INSERT INTO #TestTable VALUES ( 2, 'A', 'B', GETDATE() + 1 )
INSERT INTO #TestTable VALUES ( 3, 'A', 'C', GETDATE() + 2 )
INSERT INTO #TestTable VALUES ( 4, 'A', 'D', GETDATE() + 3 )
INSERT INTO #TestTable VALUES ( 5, 'B', 'A', GETDATE() + 4 )
INSERT INTO #TestTable VALUES ( 6, 'B', 'B', GETDATE() + 5 )
INSERT INTO #TestTable VALUES ( 7, 'B', 'C', GETDATE() + 6 )
INSERT INTO #TestTable VALUES ( 8, 'B', 'D', GETDATE() + 7 )
INSERT INTO #TestTable VALUES ( 9, 'C', 'A', GETDATE() + 8 )
INSERT INTO #TestTable VALUES ( 10, 'C', 'B', GETDATE() + 9 )
INSERT INTO #TestTable VALUES ( 11, 'C', 'C', GETDATE() + 10 )
INSERT INTO #TestTable VALUES ( 12, 'C', 'D', GETDATE() + 11 )
INSERT INTO #TestTable VALUES ( 13, 'D', 'A', GETDATE() + 12 )
INSERT INTO #TestTable VALUES ( 14, 'D', 'B', GETDATE() + 13 )
INSERT INTO #TestTable VALUES ( 15, 'D', 'C', GETDATE() + 14 )
INSERT INTO #TestTable VALUES ( 16, 'D', 'D', GETDATE() + 15 )

SELECT DISTINCT *
FROM #TestTable T
INNER JOIN
(
SELECT ID
FROM #TestTable T3
WHERE T3.TEST3 = (SELECT MAX(TEST3) FROM #TestTable T4 WHERE T3.TEST = T4.TEST)
) T2
ON T2.ID = T.ID

SELECT * FROM #TestTable t
WHERE T.TEST3 = (SELECT MAX(TEST3) FROM #TestTable T2 WHERE T2.TEST = T.TEST)

DROP TABLE #TestTable
*/

2 Comments:

At 10:58 PM, Anonymous Steve Hamilton said...

Hope you don't mind :)

I have posted a more detailed explanation of the problem space and solution on my blog. You can find it at http://www.steve-hamilton.net/2006/07/30/select-distinct-by-column/

 
At 8:31 PM, Anonymous Anonymous said...

You know ,I have some wonderland Gold,and my friend

also has some wonderland online Gold,do you kouw they

have the same meaning,Both of them can be called
wonderland money,I just want to
buy wonderland Gold,because there are many
cheap wonderland online Gold

 

Post a Comment

<< Home