CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Prasanna Kumar J
Prasanna Kumar J

Reputation: 1618

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.

Upvotes: 5

Views: 69

Answers (2)

valex
valex

Reputation: 24144

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

Upvotes: 5

John Cappelletti
John Cappelletti

Reputation: 82010

;with cte as (
    Select *
          ,RN=Row_Number() over (Partition By datecol Order By datecol)
    From   YourTable
)
Select A.* 
 From cte A
 Join (Select datecol,cnt=count(*) from YourTable Group By datecol) B
   on A.datecol=B.datecol 
   and A.RN<=ceiling(B.cnt*.3)
 Order by datecol,RN

Returns

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

Upvotes: 2

Related Questions