Geek asks and answers

MSSQL Select Top 10 Winning Points, Including Ties and At least One From Each Category

I got some help finding the top 10 results, including related records, using the following instructions

select T.EntryID, T.CategoryID, T.Score
from (
   select EntryID, CategoryID, Score,
          dense_rank() over(order by Score) as rn
   from YourTable
 ) T
where T.rn <= 10

(thanks [mikael-eriksson]: https://stackoverflow.com/users/569436/mikael-eriksson )

[question]: MSSQL Selecting the top 10 but including columns with duplicate values Here is an example of data:

EntryID CategoryID  Score
3036    1           85
3159    1           85
3039    1           84
3146    1           83
3225    1           82
3045    1           82
3047    1           80
3048    1           80
3049    1           80
3193    1           80
3098    1           80
3025    1           72
3082    1           70
3167    1           70
3122    1           67
3220    1           65
3080    1           65
3168    1           64
______________________
Total Entries >= 18

There is a requirement that at least one entry from each category in the top ten (or top, whatever it is, for example, 100), in this case there are 3 categories.

, , -10. .. 10 1 3 , 2 1 2 3.

, 1, EntryID 3220, 3080 3168 , , 2 3, :

EntryID CategoryID  Score
3036    1           85
3159    1           85
3039    1           84
3146    1           83
3225    1           82
3045    1           82
3047    1           80
3048    1           80
3049    1           80
3193    1           80
3098    1           80
3025    1           72
3082    1           70
3167    1           70
3122    1           67
3019    3           60
3800    2           54
______________________
Total Entries >= 17

, 10-, , . 5 2

EntryID CategoryID  Score
3036    1           85
3159    1           85
3039    1           84
3146    1           83
3225    1           82
3045    1           82
3019    3           60
______________________
Total Entries >= 7

3225 3045 , (3047 , , ), 2, - :

EntryID CategoryID  Score
3036    1           85
3159    1           85
3039    1           84
3146    1           83
3019    3           60
3800    2           54
______________________
Total Entries >= 6

, , , , 2, - , (. )

EntryID CategoryID  Score
3036    1           85
3159    1           85
3039    1           84
3146    1           83
3225    1           82
3045    1           82
3019    3           60
______________________
Total Entries >= 7

, , , ;)

!

+5
1

, , , , , , , .

, , , , , - - :

WITH ranked1 AS (
  SELECT
    *,
    RankByCategory = DENSE_RANK() OVER (
      PARTITION BY CategoryID
      ORDER BY Score DESC
    )
  FROM YourTable
),
ranked2 AS (
  SELECT
    *,
    FinalRank = DENSE_RANK() OVER (
      ORDER BY
        CASE RankByCategory WHEN 1 THEN 1 ELSE 2 END,
        Score DESC
    )
  FROM ranked1
)
SELECT
  EntryID,
  CategoryID,
  Score
FROM ranked2
WHERE FinalRank <= @top_n
;

CTE , , . ( CTE) - , , . , , . (, , , .)

SQL Fiddle .

+8

More articles:


All Articles