How to limit results per Date

Question

I am trying to pull the top 5 firing item from my database.
For example 2000 UserLogon Events on the 2022-08-13.
Currently when running the below query it will return the top events, but also the next 40 events as well.

Is there a way I can embed a TOP or LIMIT into the query per day instead of limiting the returned results?

Currently, my query looks like the following:


    SELECT COUNT (M.EventID) AS TopEvent,
    Name AS Event,
    CAST (MsgDate AS DATE) AS time
    FROM Events.dbo.Msg M
    JOIN Alarms.dbo.Event E ON E.EventID = M.EventID
    GROUP BY CAST (MsgDate AS DATE), Name,
    A.EventID
    ORDER BY CAST (MsgDate AS DATE), COUNT (MA.EventID) DESC

use top https://learn.microsoft.com/en-us/sql/t-sql/queries/top-transact-sql?view=sql-server-ver16 — Ian Kenney, Nov 10 '22 at 12:35
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Nov 10 '22 at 12:43

score 0 · Answer 1 · answered Nov 10 '22 at 12:34

Just use the ROW_NUMBER() function and a common table expression.

e.g.

WITH cte
AS
(
SELECT M.EventID AS Event,
        Name AS Event,
        CAST (MsgDate AS DATE) AS time,
        ROW_NUMBER() OVER (PARTITION BY MsgDate ORDER BY M.EventID) as RN

FROM Events.dbo.Msg M
JOIN Alarms.dbo.Event E ON E.EventID = M.EventID
)
SELECT *
FROM cte
WHERE cte.RN <=5;

How to limit results per Date

1 Answers1