Top 15

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 28 Jul 2006 13:31 GMT

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

Reply to this Message

John Spencer - 28 Jul 2006 13:55 GMT

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

> Hello all,
>
[quoted text clipped - 10 lines]
> GROUP BY rDate, code, MESSAGE
> ORDER BY Sum(duration) DESC

Reply to this Message