Top 15

[Guest]

Hello all,

I have a top 15 query that works fine except I would like to get the top 15
per day.

So if I have 3 dates it would show each date with 15 in the list.

How do I get it give me the top 15 of each day?

SELECT TOP 15 rDate, code, MESSAGE, Sum(duration) AS SumOfduration
FROM tbShiftHld
GROUP BY rDate, code, MESSAGE
ORDER BY Sum(duration) DESC

 

[John Spencer]

Not sure this will work, but try

SELECT rDate, code, MESSAGE, Sum(duration) AS SumOfduration
FROM tbShiftHld
GROUP BY rDate, code, MESSAGE

HAVING Sum(Duration) in (
SELECT TOP15 Sum(duration)
FROM tbShiftHld as Tmp
WHERE Tmp.RDate = tbShiftHld.rDate
GROUP BY rDate, code, MESSAGE
ORDER BY Sum(duration) DESC)

ORDER BY rDate, Sum(duration) DESC