CODEWITHSUNDEEP
sqldatabasesql-server-2008
Zeb-ur-Rehman
Zeb-ur-Rehman

Reputation: 1209

Select top Values For each day of Specified month

I am sorry if the question is silly because i'm new to SQL Server. I want to select top 5 records for each day of specified month. e.g. 

top 5 records for day 1 in month september
top 5 records for day 2 in month september
top 5 records for day 3 in month september
.
.
top 5 records for day 31 in month september

and show these all records as a one result.

Upvotes: 2

Views: 3787

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

Let's say you're checking speeding records for the month June 2012, and you wanted the top 5 speeds (by speed desc).

SELECT *
  FROM (
    SELECT *, RowNum = Row_number() over (partition by Cast(EventTime as Date)
                                          order by Speed desc)
      FROM Events
     WHERE EventTime >= '20120601'
       AND EventTime <  '20120701'
  ) X
 WHERE RowNum <= 5

Upvotes: 5

John Woo
John Woo

Reputation: 263693

Try this one,

WITH TopFiveRecords
AS
(
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY dayColumn ORDER BY colName DESC) RN
    FROM tableName
)
SELECT  *
FROM TopFiveRecords
WHERE RN <= 5 
      -- AND date condition here ....

dayColumn the column that contains the date of the month
colName the column to be sorted

Upvotes: 1

Related Questions