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:
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.
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.
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.
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;