SQL Server Distinct Question

Question

I need to be able to select only the first row for each 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?

Answer 1

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

Answer 2

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)

Answer 3

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

Answer 4

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

Answer 5

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