CODEWITHSUNDEEP
sql-server-2008
Manish Patel
Manish Patel

Reputation: 77

Getting unique values in sql server

I have a table say :

id| AccID | Subject      | Date                       
1 | 103   | Open HOuse 1 | 11/24/2011 9:00:00 AM        
2 | 103   | Open HOuse 2 | 11/25/2011 10:00:00 AM       
3 |  72   | Open House 3 | 11/26/2011 1:10:28 AM        
4 |  82   | OPen House 4 | 11/27/2011 5:00:29 PM        
5 |  82   | OPen House 5 | 11/22/2011 5:00:29 PM

From the above table, i need all the unique values for the Accid. But say, if there are two or more columns with the same Accid, then i need the one which has the smaller date (among the columns which have the same Accid)

So, from the above table, the o/p should be : 1 3 5

Can any1 please help me in this ? Thanks

Upvotes: 0

Views: 793

Answers (2)

Nick O
Nick O

Reputation: 3826

SELECT t1.*
FROM [MyTable] t1
INNER JOIN
(
    SELECT AccID, MIN(Date) Date
    FROM [MyTable]
    GROUP BY AccID
) t2 ON t1.AccID = t2.AccID AND t1.Date = t2.Date

Upvotes: 1

n8wrl
n8wrl

Reputation: 19765

More than just the AccID but...

WITH SEL
AS
(
    SELECT AccID, MIN(DATE)
      FROM table
     GROUP BY AccID
)
SELECT table.*
  FROM table
  JOIN SEL ON SEL.AccID = table.AccID

Upvotes: 1

Related Questions