Get top 5 on 2 distinct where clause On postgres I have data like:

id | device_model | device_type
1  | "samsung"    | 1
2  | "iphone 4"   | 1
3  | "samsung"    | 1
4  | "ipad"       | 0
5  | "ipad"       | 0


And I'm looking to get the top 5 of device_model per device_type.

for the moment I'm doing :

SELECT device_model, COUNT(*)
FROM devices
GROUP BY device_model  
ORDER BY COUNT(*) DESC
limit 5;


But it doesn't render top5 for each device_type. What can I use to do this? Seems I need to do a UNION.

Can I do something like :

SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 1
GROUP BY device_model  
ORDER BY COUNT(*) DESC
LIMIT 5 
UNION
SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 0
GROUP BY device_model  
ORDER BY COUNT(*) DESC
LIMIT 5;

ERROR:  syntax error at or near "UNION"
LINE 7: UNION ALL
        ^
Query failed
PostgreSQL said: syntax error at or near "UNION"
This is the right query:

WITH counts AS (
    SELECT device_model, device_type, COUNT(id) AS cnt, rank() OVER (PARTITION BY device_type ORDER BY COUNT(id) DESC) AS model_rank
    FROM devices
    GROUP BY device_model, device_type
)
SELECT device_model, device_type, cnt, model_rank
FROM counts
WHERE model_rank &lt;= 5
ORDER BY device_type, model_rank


It handles models that has the same rank, displaying more than 5 rows per device type just in case.

In order to test it, create a table and fill it with data:

CREATE TABLE devices (
    id serial,
    device_model text,
    device_type integer
);

INSERT INTO devices (device_model, device_type)
    VALUES
        ('samsung', 1),
        ('iphone4', 1),
        ('samsung', 1),
        ('iphone4', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('iphone6', 1),
        ('iphone3', 1),
        ('iphone3', 1),
        ('iphone3', 1),
        ('iphone2', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('kindle', 0),
        ('kindle', 0),
        ('kindle', 0),
        ('kindle', 0)
;


The output of the initial query is the following:

 device_model | device_type | cnt | model_rank 
--------------+-------------+-----+------------
 ipad         |           0 |   5 |          1
 kindle       |           0 |   4 |          2
 iphone1      |           1 |   6 |          1
 samsung      |           1 |   6 |          1
 iphone3      |           1 |   3 |          3
 iphone4      |           1 |   2 |          4
 iphone6      |           1 |   1 |          5
 iphone2      |           1 |   1 |          5
(8 rows)


Feel free to ask questions about the logic behind this solution.