Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What will be easier way to group records by range of percents.

Here what I try to accomplish

Select top percents between 50 and 75 * from myTable order by PKI

Here is way I did this but I have felling that it cloud be much much easier

select 
    top(25) percent 
     *
from myTable
where id not in (
            select 
                top(25) percent 
                 ID
            from myTable
            order by PKI
            )
order by PKI

This should be top 50 - 75 percents of my table ?

Is there better (nicer) way to do this.

share|improve this question
1  
What version of SQL Server? –  Martin Smith Jan 16 '13 at 14:16
    
@MartinSmith 2008R2 –  adopilot Jan 16 '13 at 14:18
add comment

2 Answers

up vote 6 down vote accepted

Another way of doing it would be

WITH CTE
     AS (SELECT ID,
                PKI,
                ROW_NUMBER() OVER ( ORDER BY PKI) AS RN,
                COUNT(*) OVER()                   AS Cnt
         FROM   myTable)
SELECT ID,
       PKI
FROM   CTE
WHERE  RN BETWEEN 0.5 * Cnt AND 0.75 * Cnt 
share|improve this answer
add comment

Another way would be the use of NTILE() function:

WITH cte AS
  ( SELECT id,
           pki,
           NTILE(4) OVER(ORDER BY pki) AS quartile
    FROM  myTable
  )
SELECT id,
       pki
FROM  cte
WHERE quartile = 3 ;
share|improve this answer
    
That doesn't always return the same as a percentage based calculation would but I guess with only 4 buckets it would be fine (the issue is that if the number of rows is not evenly divisible by number of buckets the extra ones all get added to the first buckets) –  Martin Smith Jan 16 '13 at 14:42
    
@MartinSmith Yes, this behaviour can give rather strange results when the number of rows is small and the NTILE parameter big. –  ypercube Jan 16 '13 at 14:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.