Prasanna Kumar J Prasanna Kumar J - 10 days ago 6
SQL Question

how to get top rows from particular approx. percentage using sql server 2008?

i need approx. 30 percentage data from each date.

id name datecol
-----------------------
1 A 2016-11-11
2 B 2016-11-11
3 C 2016-11-11
4 D 2016-11-11
5 E 2016-11-11
6 F 2016-11-11
7 G 2016-11-11
8 H 2016-11-11
9 I 2016-11-11
10 J 2016-11-11
11 A1 2016-11-12
12 B1 2016-11-12
13 C1 2016-11-12
14 D1 2016-11-13
15 E1 2016-11-13
16 F1 2016-11-14
17 G1 2016-11-14
18 H1 2016-11-14
19 I1 2016-11-14
20 J1 2016-11-14



In this case i have


10 rows in 2016-11-11
3 rows in 2016-11-12
2 rows in 2016-11-13

5 rows in 2016-11-14


i need like this by approx. 30 percentage of top rows from each date,


id name datecol
-----------------------
1 A 2016-11-11
2 B 2016-11-11
3 C 2016-11-11
11 A1 2016-11-12
14 D1 2016-11-13
16 F1 2016-11-14
17 G1 2016-11-14


Thanks in Advance.

Answer

Try this query using ROW_NUMBER() to get a row number and COUNT() OVER () to get a total count for each date:

WITH CTE AS 
(
  SELECT T.*,
         ROW_NUMBER() OVER (PARTITION BY datecol ORDER BY Name) as RowNum,
         COUNT(*) OVER (PARTITION BY datecol) as Total
    FROM Table as T
)
SELECT id,name,datecol 
  FROM CTE 
 WHERE RowNum <= CEILING(Total*0.30)

Result:

1   A   2016-11-11
2   B   2016-11-11
3   C   2016-11-11
11  A1  2016-11-12
14  D1  2016-11-13
16  F1  2016-11-14
17  G1  2016-11-14