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

Let's say I have a table:

 -------------- -------------- ------ ----- 
| ID        | Score   | email           | add | 
 -------------- -------------- ------ ----- 
| 123       | 88      | [email protected]   | somewhere | 
| 456       | 77      | [email protected]   |  somewhere   | 
| 789       | 88      | [email protected]   |   somewhere  |     
| 111       | 77      |[email protected]    |   somewhere  |    
| 555       | 77      | [email protected]   |   somewhere  |   
|444        | 88      | [email protected]   |    somewhere
| 222       | 77      | [email protected]   |  somewhere |
| 333       | 88     |[email protected]     | 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

CodePudding user response：

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;