All Forums Database
farrukh.kayani 3 posts Joined 01/14
29 Apr 2015
Top 10 rows for each date

I need to generate a report for top 10 rows for each date in a table. I can write a statment for each date but can anyone help with writitng it in single query;

select top * from Table where date ='2014-01-01';
select top * from Table where date ='2014-02-01';
select top * from Table where date ='2014-03-01';

Looking for any help.
Br,
FK
 
 

Br, Farrukh
Tags:
CarlosAL 512 posts Joined 04/08
29 Apr 2015

Hi.
If you need TOP only for SAMPLING (whithout ORDER BY) you could try stratified sampling.
 
SELECT *
  FROM TheTable
SAMPLE WHEN TheDate = '2014-01-01' THEN 10
            WHEN TheDate = '2014-2-01' THEN 10
...
END;
 
HTH
Cheers.
Carlos.
 

manib0907 61 posts Joined 04/15
29 Apr 2015
Select * FROM Table Qualify Row_number()Over( partition by date order by column) between 1 and 10;

I think this should work..

Cheers,
Mani

farrukh.kayani 3 posts Joined 01/14
29 Apr 2015

Thanks Mani. It works. :)
 

Br,
Farrukh

You must sign in to leave a comment.