CODEWITHSUNDEEP
sqlite
Adm
Adm

Reputation: 37

sqlite3 querying certain number of rows of each id

I would like to query a database like this:

table person

id  name     age    weight
1   teste1   18     101
1   teste2   18     102
1   teste3   18     103
1   teste4   18     104
1   teste5   18     105
1   teste6   18     106
2   teste7   18     91  
2   teste8   18     92
2   teste9   18     93
2   teste9   18     94
2   teste1   18     95
2   teste2   18     96
3   teste3   18     87
3   teste3   18     88
3   teste3   18     89
3   teste3   18     81
3   teste3   18     82
3   teste3   18     83
3   teste3   18     84
3   teste3   18     85

and the result should be the 3 highest weight of each id, like this:

id  name     age    weight
1   teste4   18     106
1   teste5   18     105
1   teste6   18     104
2   teste9   18     96
2   teste1   18     95
2   teste2   18     94
3   teste3   18     89
3   teste3   18     88
3   teste3   18     87

can someone help me? Best regards

Upvotes: 0

Views: 182

Answers (1)

forpas
forpas

Reputation: 164089

With ROW_NUMBER() window function:

select t.id, t.name, t.age, t.weight
from (
  select *, row_number() over (partition by id order by weight desc) rn
  from tablename
) t
where t.rn <= 3
order by t.id, t.weight desc

See the demo.

Without window functions you can use a correlated subquery in the WHERE clause:

select t.id, t.name, t.age, t.weight
from tablename t
where (select count(*) from tablename where id = t.id and weight >= t.weight) <= 3
order by t.id, t.weight desc;

See the demo.

Results:

| id  | name   | age | weight |
| --- | ------ | --- | ------ |
| 1   | teste6 | 18  | 106    |
| 1   | teste5 | 18  | 105    |
| 1   | teste4 | 18  | 104    |
| 2   | teste2 | 18  | 96     |
| 2   | teste1 | 18  | 95     |
| 2   | teste9 | 18  | 94     |
| 3   | teste3 | 18  | 89     |
| 3   | teste3 | 18  | 88     |
| 3   | teste3 | 18  | 87     |

Upvotes: 2

Related Questions