How can I select distinct records by a name?

Question

My table Tags has these records

Application   ID                                      TagName
/Blogs        A75FB4D9-B0A2-45B1-A58D-9CC4E7FC1482    TagA
/News         E1BDEF9D-4285-464F-88DC-00495B59D2AE    TagA
/News         997F1721-335B-477A-9943-B91F0C21DE74    TagB
/Blogs        BB1CEE87-AF8A-44D6-8A4B-EAB138BBEF10    TagB

I want to return only the first match of TagA and TagB, but the returning table must look exactly like the above (Application, ID, TagName), so I expect it to be:

/Blogs        A75FB4D9-B0A2-45B1-A58D-9CC4E7FC1482    TagA
/News         997F1721-335B-477A-9943-B91F0C21DE74    TagB

How can I do that?

Answer 1

SQL Server 2005+

 SELECT Application, Id, TagName
 FROM
 (SELECT Application, Id, TagName,
  ROW_NUMBER()  OVER (partition by TagName order by TagName) rn
  FROM Table
 ) x 
 WHERE rn =1

Answer 2

This might do the trick...

SELECT MIN(Application), MIN(ID), TagName FROM Table GROUP BY TagName

In this example you will get an id that is not predictable...

Answer 3

In the intesest of providing a non sql server specific answer

SELECT Application, Id, TagName
FROM Tags t
JOIN (
    SELECT Application, TagName, MAX(InsertOrder) LatestInsert
    FROM Tags
    GROUP BY Application, TagName
) l ON l.Application = t.Application AND l.TagName = t.TagName AND l.LatestInsert = t.InsertOrder

This assumes the existance of a column which allows you to identify a "first" match - note that if this is a timestamp its possible you may have two records with an identical insert time