0

I have a query which selects a max value over all the values of in a datetime column (dtimesent).

select dtimesent, max(ResponseTime) AS maxtime
FROM allrequests 
where Request like '%Requestsa%'
group by dtimesent

I need to get the value of the request colmn for each max response time in each group by. Is there any easy way to do this? There can be multiple rows per dtimesent value so I can't just include the request column in the group by,

Many thanks,

gbn
  • 69,809
  • 8
  • 163
  • 243
dublintech
  • 1,519
  • 8
  • 21
  • 26

1 Answers1

4

RANK will deal with multiple max values (instead of ROW_NUMBER)

SELECT
   dtimesent, Request, ResponseTime
FROM
    (
    select dtimesent, Request, ResponseTime ,
       RANK() OVER (ORDER BY ResponseTime DESC) AS rn
    FROM allrequests 
    where Request like '%Requestsa%'
    ) T
WHERE
   rn = 1

Also see How to get the MAX row please

Community
  • 1
gbn
  • 69,809
  • 8
  • 163
  • 243