sforsandeep sforsandeep - 5 months ago 5
SQL Question

SQL query to take top N number of rows, but should include atleast one row from each groups based on a column

I have a SQL table like this

ID JobZone Title
-------------------------------
1 1 Job1
2 1 Job2
3 1 Job3
4 1 Job4
5 2 Job5
6 2 Job6
7 2 Job7
8 2 Job8
9 3 Job9
10 3 Job10
11 4 Job11
12 4 Job12
13 5 Job13
14 4 Job14
15 5 Job15
16 6 Job16
17 7 Job17
18 7 Job18
19 7 Job19
20 8 Job20


I need to select top 2 rows from all jobzones. Means it should include at least 1 rows from every jobzones if there is no 2 rows per jobzones.

How can I make it possible?

I tried

;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY JobZone ORDER BY title) AS rn
FROM mytable
)
SELECT *
FROM cte
WHERE rn = 1


Also I tried
DENSE_RANK()
instead of
ROW_NUMBER()
which also did not work as I expected.

So the result should be something like this

ID JobZone Title
-------------------------------
1 1 Job1
2 1 Job2
5 2 Job5
6 2 Job6
9 3 Job9
10 3 Job10
11 4 Job11
12 4 Job12
13 5 Job13
15 5 Job15
16 6 Job16
17 7 Job17
18 7 Job18
20 8 Job20

Answer

If you want the top 2 of each job zone, you just need to change your WHERE:

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY JobZone ORDER BY title) AS rn
   FROM mytable
)
SELECT *
FROM cte
WHERE rn <= 2

For your scenario, you already have the ROW_NUMBER correct. Here's a quick breakdown of the 3 usual choices:

  • ROW_NUMBER - Assigns unique numbers to each row within the partition in the sequence of your order clause ({10, 10, 20} would ROW_NUMBER to {1, 2, 3})
  • RANK - Assigns unique numbers to each unique value leaving gaps for rows that have the same value (ie: {10, 10, 20} would RANK to {1, 1, 3})
  • DENSE_RANK - Same as RANK except no gaps are left ({10, 10, 20} would DENSE_RANK to {1, 1, 2})