CODEWITHSUNDEEP
sqlsql-server
lamwaiman1988
lamwaiman1988

Reputation: 3742

Sql to select row from each day of a month

I have a table which store records of all dates of a month. I want to retrieve some data from it. The table is so large that I should only selecting a fews of them. If the records have a column "ric_date" which is a date, how can I select records from each of the dates in a month, while selecting only a fews from each date?

The table is so large that the records for 1 date can have 100000 records.

Upvotes: 0

Views: 3682

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Rough idea. This will get the first three rows per day for the current month (or as many that exist for any given day - there may be days with no rows represented).

DECLARE 
    @manys INT = 3, 
    @month DATE = DATEADD(DAY, 1-DAY(GETDATE()), DATEDIFF(DAY, 0, GETDATE()));

;WITH x AS 
(
  SELECT some_column, ric_date, rn = ROW_NUMBER() OVER 
    (PARTITION BY ric_date ORDER BY ric_date)
  FROM dbo.data 
  WHERE ric_date >= @month
  AND ric_date < DATEADD(MONTH, 1, @month)
)
SELECT some_column, ric_date FROM x
WHERE rn <= @manys;

If you don't have supporting indexes (most importantly on ric_date), this won't necessarily scale well at the high end.

Upvotes: 0

J Cooper
J Cooper

Reputation: 4988

WITH T AS (
    SELECT ric_date
    FROM yourTable
    WHERE rice_date BETWEEN @start_date AND @end_date -- thanks Aaron Bertrand
    GROUP BY ric_date
)

SELECT CA.*
FROM T
CROSS APPLY (
    SELECT TOP 500 * -- 'a fews'
    FROM yourTable AS YT
    WHERE YT.ric_date = T.ric_date
    ORDER BY someAttribute -- not required, but useful
) AS CA

Upvotes: 1

Related Questions