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.
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
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.
