John John - 4 days ago 5
SQL Question

Return a limit of 2 records for each distinct column value

Assume I have a table that looks like this:

| Col A | Col B | Col C |
|-------|-------|-------|
| 1 | A | 54 |
| 1 | A | 56 |
| 1 | B | 55 |
| 1 | B | 51 |
| 1 | C | 36 |
| 1 | C | 23 |
| 1 | D | 62 |
| 1 | D | 11 |
| 2 | B | 88 |
| 2 | B | 17 |
| 2 | C | 56 |
| 2 | C | 86 |
| 2 | D | 47 |
| 2 | D | 29 |


What I want to do is grab the table to look like this:

| Col A | Col B | Col C |
|-------|-------|-------|
| 1 | A | 54 |
| 1 | A | 56 |
| 2 | B | 88 |
| 2 | B | 17 |


I'm pretty sure there is a way to do this, I just don't know how. First, I thought a
DISTINCT ON
selector would work, but that only returns one record for each value. In this case, I need two records for each value.

Answer

One way to do this would be to use a window function to add a row number to each partition of data ordered by however you want and then select the anything with a row number less than 2.

With CTE AS (
SELECT colA, ColB, ColC, Row_Number() over (Partition by ColA ORDER By  ColB , ColC) RN
FROM Table)
Select * from cte where RN <=2

Since I didn't know what values of c you wanted, I choose to order by colC (ascending) so the lowest values of C would be returned for a given A+B combination.

Comments