Distinct SQL Query
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 开发者_StackOverflow社区won't work because of the ID field.
SELECT DISTINCT
[ID], [Name]
FROM
MyTable
SELECT MIN(ID) AS ID, [Name]
FROM MyTable
GROUP BY [Name]
This will return the first (i.e. MINimum) ID for each distinct Name
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
精彩评论