Select top 10 percent, also bottom percent in SQL

Question

I have two questions:

1. When using the select top 10 percent statement, for example on a test database with 100 scores, like this:

   Select top 10 percent score 
   from test
   

   Would SQL Server return the 10 highest scores, or just the top 10 obs based on how the data look like now (e.g. if the data is entered into database in a way that lowest score appears first, then would this return the lowest 10 scores)?

2. I want to be able to get the top 10 highest scores and bottom 10 lowest scores out of this 100 scores, what should I do?

Answer 1

Using a UNION ALL means that it will count all rows twice.

You can do it with a single count as below. Whether or not this will be more efficient will depend (e.g. on indexes).

WITH T
     AS (SELECT *,
                1E0 * ROW_NUMBER()
                        OVER (
                          ORDER BY score) / COUNT(*)
                                                  OVER() AS p
         FROM   test)
SELECT *
FROM   T
WHERE  p < 0.1
        OR p > 0.9 

Answer 2

You could also use the NTILE window function to group your scores into 10 groups of data - group no. 1 would be the lowest 10%, group no. 10 would be the top 10%:

;WITH Percentile AS
(
    SELECT
        Score,
        ScoreGroup = NTILE(10) OVER(ORDER BY Score)
    FROM
        test
)
SELECT * 
FROM Percentile
WHERE ScoreGroup IN (1, 10)

Answer 3

select score from 
    (Select top 10 percent score
     from test
     order by score desc
    )a
union all   
select score from 
   (select top 10 percent score
    from test
    order by score asc
   )b

if duplicates are allowed use union

Answer 4

Use ascending in your query for the top 90. Then, descending in your query for the top 10. Then, union these two queries