Distinct SQL Query

Question

I have a SQL Server 2008 database with the following information in a table:

ID  Name
--  ----
1   John
2   Jill
3   John
4   Phil
5   Matt
6   Jill

I want to display the unique names in a drop down list. Because of this, I need just one of the IDs associated with the unique name. I know it's dirty. I didn't create this mess. I just need the unique names with one of the ids. How do I write a query that will do that? I know that the following won't work because of the ID field.

SELECT DISTINCT
  [ID], [Name]
FROM
  MyTable

Answer 1

You could also do it with rank over function

SELECT
Id,
Name
FROM
(
    SELECT 
    Id,
    [Name],
    RANK() OVER (PARTITION BY [Name] Order By Id) As Idx
    FROM Test
) A
WHERE Idx = 1

To get understanding about rank over function read this: http://msdn.microsoft.com/en-us/library/ms176102.aspx

Answer 2

SELECT MIN(ID) AS ID, [Name]
FROM MyTable
GROUP BY [Name]

This will return the first (i.e. MINimum) ID for each distinct Name