开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜