In SQLite3, is it possible to list out specific rows?

Question

Let's say I have a table:

+--------------+--------------+------+-----+
| ID        | Score   | email           | add | 
+--------------+--------------+------+-----+
| 123       | 88      | 123@gmail.com   | somewhere | 
| 456       | 77      | 123@gmail.com   |  somewhere   | 
| 789       | 88      | 123@gmail.com   |   somewhere  |     
| 111       | 77      |123@gmail.com    |   somewhere  |    
| 555       | 77      | 123@gmail.com   |   somewhere  |   
|444        | 88      | 123@gmail.com   |    somewhere
| 222       | 77      | 123@gmail.com   |  somewhere |
| 333       | 88     |123@gmail.com     | somewhere    |     

My question is it possible to select Score column and ONLY print out first 3 88 and 77 Score?

I tried but it seems only give me 3 88 scores only

SELECT Score 
FROM Table_Name
WHERE Score = '88' OR Score = '77'
LIMIT 3

Answer 1

First filter the table so that only the rows with the scores that you want are returned and then use ROW_NUMBER() window function to rank the rows of each Score based on ID so that you can filter out the rows that exceed the first 3 of each group:

SELECT ID, Score, email, add
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Score ORDER BY ID) rn
  FROM Table_Name
  WHERE Score = '88' OR Score = '77'
)
WHERE rn <= 3;

For versions of SQLite prior to 3.25.0 that do not support window functions use a correlated subquery:

SELECT t1.*
FROM Table_Name t1
WHERE t1.Score = '88' OR t1.Score = '77'
  AND (SELECT COUNT(*) FROM Table_Name t2 WHERE t2.Score = t1.Score AND t2.ID <= t1.ID) <= 3;