Getting records with month by month, year by year and day by day from SQL Server

I’m looking for a fast and easy SQL Query to get records year by year, month by month and day by day.

My database example:

ID - DATE        - CatID - VALUE
1  - 2013-08-06  -  32   -  243
2  - 2013-08-16  -  2    -  45
3  - 2013-08-21  -  2    -  1
4  - 2013-08-05  -  32   -  450
5  - 2013-08-05  -  32   -  449
6  - 2013-08-05  -  32   -  11
7  - 2013-08-01  -  2    -  221
8  - 2013-08-02  -  32   -  0
9  - 2013-08-02  -  32   -  0
10 - 2013-08-02  -  32   -  987
...
..

I have over 2 millions records on this table.

First:

I would like to get CatID = 32 and single/higher record of the day.

Result:

1  - 2013-08-06  -  32   -  243
4  - 2013-08-05  -  32   -  450
10 - 2013-08-02  -  32   -  987

Second:

I would like to get as same but by MONTH.

How can I do it with SQL SERVER. Currently Sql Server 2012.

Also using C# 5 and if you want to use, EF 6.

Down voters, this question became “popular question”. Now please explain why down voted?

Answer

The below assumes that you want more than just the max value for each date/month, but also want to know the id of that row, etc, etc.

For daily…

WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY CatID,
                                    Date
                           ORDER BY Value DESC)   AS ordinal_cat_date
  FROM
    yourTable
)
SELECT
  *
FROM
  sorted
WHERE
  ordinal_cat_date = 1
  AND catID = 32

For monthly…

WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY CatID,
                                    DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0)
                           ORDER BY Value DESC)   AS ordinal_cat_month
  FROM
    yourTable
)
SELECT
  *
FROM
  sorted
WHERE
  ordinal_cat_month = 1
  AND catID = 32

In both cases the ROW_NUMBER() function creates a sequence of numbers (1,2,3,4,etc) for each PARTITION of catID, date, in descending order of value. Whichever row has a value of 1 is the row with the highest value in that partition.

The formula DATEADD(MONTH, DATEDIFF(MONTH, 0, Date), 0) just rounds the Date down to the first day of the month, thereby creating partitions for whole months.

Some other options that bring through multiple rows per day/month if those rows all share the same highest value…

SELECT  -- Daily Version
  *
FROM
  yourTable
WHERE
  NOT EXISTS (SELECT *
                FROM yourTable   AS lookup
               WHERE lookup.CatID = yourTable.CatID
                 AND lookup.Value > yourTable.Value
                 AND lookup.Date  = yourTable.Date
             )
  AND CatID = 32

SELECT  -- Monthly Version
  *
FROM
  yourTable
WHERE
  NOT EXISTS (SELECT *
                FROM yourTable   AS lookup
               WHERE lookup.CatID  = yourTable.CatID
                 AND lookup.Value >  yourTable.Value
                 AND lookup.Date  >= DATEADD(MONTH,   DATEDIFF(MONTH, 0, yourTable.Date), 0)
                 AND lookup.Date  <  DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, yourTable.Date), 0)
             )
  AND CatID = 32

Or possibly…

SELECT  -- Daily Version
  *
FROM
  yourTable
INNER JOIN
(
  SELECT
    Date,
    CatID,
    MAX(Value)   AS max_value
  FROM
    yourTable
  GROUP BY
    CatID,
    Date
)
  AS lookup
    ON  yourTable.Date  = lookup.Date
    AND yourTable.CatID = lookup.CatID
    AND yourTable.Value = lookup.max_value
WHERE
  yourTable.CatID = 32

SELECT  -- Monthly Version
  *
FROM
  yourTable
INNER JOIN
(
  SELECT
    CatID,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, yourTable.Date), 0)   AS month_start,
    MAX(Value)   AS max_value
  FROM
    yourTable
  GROUP BY
    CatID,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, yourTable.Date), 0)
)
  AS lookup
    ON  yourTable.Date  >= lookup.month_start
    AND yourTable.Date  <  DATEADD(MONTH, 1, lookup.month_start)
    AND yourTable.CatID  = lookup.CatID
    AND yourTable.Value  = lookup.max_value
WHERE
  yourTable.CatID = 32