SQL Server Distinct Question

I need to be able to select only the first row for e开发者_高级运维ach name that has the greatest value.

I have a table with the following:

id  name  value

0   JOHN  123
1   STEVE 125
2   JOHN  127
3   JOHN  126

So I am looking to return:

id  name  value

1   STEVE 125
2   JOHN  127

Any idea on the MSSQL Syntax on how to perform this operation?

While you specified SQL Server, you did not specify the version. If you are using SQL Server 2005 or later, you can do something like:

With RankedItems As
    (
        Select id, name, value
            , Row_Number() Over ( Partition By name Order By value Desc, id Asc ) As ItemRank
        From Table
    )
Select id, name, value
From RankedItems
Where ItemRank = 1

try:

SELECT
    MIN(id) as id,dt.name,dt.value
    FROM (SELECT
              name,MAX(value) as value
              FROM YourTable
              GROUP BY name
         ) dt
        INNER JOIN YourTable t ON dt.name=t.name and dt.value=t.value
    GROUP BY dt.name,dt.value

try it out:

DECLARE @YourTable table (id int, name varchar(10), value int)
INSERT @YourTable VALUES (0, 'JOHN',  123)
INSERT @YourTable VALUES (1, 'STEVE', 125)
INSERT @YourTable VALUES (2, 'JOHN',  127)
INSERT @YourTable VALUES (3, 'JOHN',  126)

--extra data not in the question, shows why you need the outer group by
INSERT @YourTable VALUES (4, 'JOHN',  127)
INSERT @YourTable VALUES (5, 'JOHN',  127)
INSERT @YourTable VALUES (6, 'JOHN',  127)
INSERT @YourTable VALUES (7, 'JOHN',  127)

SELECT
    MIN(id) as id,dt.name,dt.value
    FROM (SELECT
              name,MAX(value) as value
              FROM @YourTable
              GROUP BY name
         ) dt
        INNER JOIN @YourTable t ON dt.name=t.name and dt.value=t.value
    GROUP BY dt.name,dt.value
    ORDER BY id

output:

id          name       value
----------- ---------- -----------
1           STEVE      125
2           JOHN       127

(2 row(s) affected)

You could do something like

SELECT id, name, value
FROM (SELECT id, name, value
             ROWNUMBER() OVER (PARTITION BY name ORDER BY value DESC) AS r
      FROM table) AS x
WHERE x.r = 1 ;

This will not work in SQL Server 2000 and earlier, but it will be incredibly fast in SQL Server 2005 and 2008

How about:

SELECT a.id, a.name, b.maxvalue
  FROM mytbl a
 INNER JOIN (SELECT id, max(value) as maxvalue
               FROM mytbl
           GROUP BY id) b ON b.id = a.id

SELECT a.id, a.name, a.value
  FROM mytbl a
 INNER JOIN (SELECT name, max(value) as maxvalue
               FROM mytbl
           GROUP BY name) b ON b.name = a.name and b.maxvalue = a.value