CODEWITHSUNDEEP
mysqlsql
Pankaj
Pankaj

Reputation: 2744

SQL Query for getting maximum value from a column

enter image description here

I have got this data in the database about users spending time on a particular activity.

I intend to get the data when every user has spent the maximum number of hours.

Something like this:

enter image description here

Select Id, Name, HoursSpent, Date from HoursSpent
    Where HoursSpent = (SELECT MAX(HoursSpent) FROM HoursSpent)

But it is only giving me rows for duplicate data with maximum hours, I would like to get the users as well who don't have duplicate data like Person2 and Person9 as well.

Upvotes: 1

Views: 136

Answers (3)

Lajos Arpad
Lajos Arpad

Reputation: 76424

This is how you can get the maximum hours per user:

select hs.Name, max(hs.HoursSpent)
from HoursSpent hs
group by hs.Name;

Now, let's do some joining:

select hs2.Id, hs2.Name, hs2.HoursSpent, hs2.Date
from HoursSpent hs2
join (
    select hs.Name, max(hs.HoursSpent)
    from HoursSpent hs
    group by hs.Name;
) hs
on hs2.Name = hs.Name and hs2.HoursSpent = hs.HoursSpent;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

I would use ROW_NUMBER here:

SELECT Id, Name, HoursSpent, Date
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY HoursSpent DESC) rn
    FROM HoursSpent
) t
WHERE rn = 1;

If instead you want to find more than one record per name which might be tied for the maximum number of hours spent, then replace ROW_NUMBER with RANK.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You want a correlation clause:

select hs.Id, hs.Name, hs.HoursSpent, hs.Date
from HoursSpent hs
where hs.HoursSpent = (select max(hs2.HoursSpent) 
                       from HoursSpent hs2
                       where hs2.name = hs.name
-----------------------------^ this is the correlation clause
                      );

This says that for each name in HoursSpent, choose the row that has the maximum value of HoursSpent.

Upvotes: 2

Related Questions