I'm having a problem with selecting a single row when there are multiple entries for one "callid"
In this case there are two rows being selected:
Assignee maxTime
Jim Smith 11:31:05
James Smith 17:50:16
I want to only select a single row that has the greatest time.
Output I want:
Assignee maxTime
James Smith 17:50:16
This is my code:
select Assignee, MAX(TimeResolv) as maxTime
from heat8..asgnmnt
where callid ='00539265'
and GroupName like '%cs%'
Group by Assignee
Help would be appreciated.
You can use TOP :
SELECT TOP 1 *
FROM heat8..asgnmntt t
ORDER BY t.timeResolv DESC
Or less efficient with NOT EXISTS():
SELECT * FROM heat8..asgnmntt t
WHERE NOT EXISTS(SELECT 1 FROM heat8..asgnmnt s
WHERE s.timeResolv > t.timeResolv)
Or with window function ROW_NUMBER() :
SELECT s.Assignee, s.TimeResolv
FROM (
SELECT t.*,
ROW_NUMBER() OVER(ORDER BY t.timeResolv) as rnk
FROM heat8..asgnmntt t) s
WHERE s.rnk = 1
ROW_NUMBER() is also good to do it with one query for results per group.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments