lundi 1 juin 2015

SQL - Select first n occurrences of each value

Let's say I have a table like so, only with thousands of records.

| Foo | Bar  | 
| 1   | A_1  | 
| 1   | A_2  | 
| 2   | B_1  | 
| 3   | B_1  | 
| 3   | B_2  | 
| 3   | B_1  | 
| 4   | B_3  | 
...

When I execute the code below, I obviously get each and every record where Bar begins with B.

SELECT Foo
FROM tableName
WHERE Bar LIKE 'B%'

I know using GROUP BY will get me the first 1 record of each, but I need more than just that. Using SELECT TOP 100 will only net me the first 100 records, regardless of which "B" they are, and since there are far more than 100 B_1 values, that's all that I will get.

How can I only get the first n records for each unique B? For example, the first 100 occurrences of B_1, the first 100 of B_2, etc.

Aucun commentaire:

Enregistrer un commentaire