CODEWITHSUNDEEP
sqlsqlite
Flametrav
Flametrav

Reputation: 177

Max of column sqlite

I have a table like this:

Table: Albums

ID  NAME  USER_ID
---
1   Selfie 1
2   Me and VIPs 1
3   Me  2
4   My pictures 5
5   images  7
6   My POTUS Images 8
7   My Selfies  10

the output should be something like

MaxAlbumAUserHas: 2

Because the user with id 1 has 2 albums

I want to get the user who has most albums, how can I query this?

Thanx in advance

Upvotes: 0

Views: 49

Answers (2)

forpas
forpas

Reputation: 164214

You can group by user, sort the results by the number of albums descending and return the top row:

select 'MaxAlbumAUserHas: ' || count(*) result
from Albums
group by user_id
order by count(*) desc limit 1;

or with count() window function without the need of group by:

select 'MaxAlbumAUserHas: ' || count() over (partition by user_id) result
from Albums
order by count() over (partition by user_id) desc limit 1;

See the demo.
Results:

| result              |
| ------------------- |
| MaxAlbumAUserHas: 2 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You need group by to count the albums. Then order and limit to the number of rows that you want:

select userid
from t
group by userid
order by count(*) desc
limit 1;

Upvotes: 0

Related Questions