CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008
jacky brown
jacky brown

Reputation: 271

How can I get biggest value for each name

I have the next table:

name:history

columns:

id(primary key),name,value

values:

1, dani, 50

2, dani, 100

3, john, 100

4, john, 150

How can I get for each name the biggest value he has, so the result will be:

2, dani, 100

4, john, 150

thanks!

Upvotes: 1

Views: 142

Answers (6)

PaPa
PaPa

Reputation: 1

select T1.*  
from history as T1  
inner join (select MAX(value) as value,name  
         from history
         group by name) as T2
on T1.name = T2.name
and T1.value = T2.value

Upvotes: 0

Soheil Farahani
Soheil Farahani

Reputation: 359

SELECT id,
       name,
       MAX(value) 
FROM   history 
GROUP BY id,
         name
ORDER BY id

Upvotes: 0

Dalex
Dalex

Reputation: 3625

There is one small nuance: when you will have the same value twice inside of the name group, you should not get this row twice. Let us assume that if you will have the same value, last id should be returned:

  SELECT DISTINCT t2.MaxId, h.name, h.value
    FROM history h
    JOIN (
        SELECT name, MAX(value) as value
        FROM history GROUP By name
    ) t ON h.name = t.name and h.value = t.VALUE
    JOIN (
    SELECT MAX(id) MAxid,name,value FROM
    history
    GROUP BY name,VALUE
    ) t2 ON [t2].[name]=[h].NAME AND [t2].[value]=[h].value

But i strongly recommend to use on SQL Server one of window functions, for example RANK:

;    WITH   C
    AS     (SELECT H.id,
                   H.name,
                   H.value,
                   RANK() OVER (PARTITION BY Name ORDER BY value DESC, id DESC) 
                      AS [ValueRank]
            FROM   history AS H)
    SELECT c.id,
           name,
           c.value
    FROM   c
    WHERE  ValueRank = 1;

Last query has much better performance.

Upvotes: 0

Michał Powaga
Michał Powaga

Reputation: 23183

SELECT id, name, value
FROM history h
JOIN (
    SELECT name, MAX(value) as value
    FROM history GROUP By name;
) t ON h.name = t.name and h.value = t.value

Upvotes: 3

user359040
user359040

Reputation: 

select max(id), name, max(value) from history group by name

Upvotes: 5

Māris Kiseļovs
Māris Kiseļovs

Reputation: 17295

SELECT id,name,MAX(value) FROM history GROUP By name;

Upvotes: 3

Related Questions