How to use conditions with a RANK statement

Question

The following piece of code does its job : it gives me the top 10 results for each category.

SELECT *
FROM (
SELECT *, RANK() OVER (PARTITION BY "pera_id" ORDER BY "surface" DESC) AS rnk
FROM "testBadaBing"
) AS x
WHERE rnk <= 10

Now I'd like to add conditions so that the number of results may vary based on a criteria. Example : if "note" = 1, then I want to retain 1 result, else make it 3.

I tried something along the lines which you can see below using the CASE WHEN statement but as you might expect it doesn't work. Error returned :

1 - near "CASE": syntax error

SELECT *
CASE WHEN "note" = 1 THEN
SELECT *
    FROM (
        SELECT *, RANK() OVER (PARTITION BY "pera_id" ORDER BY "surface" DESC) AS rnk
        FROM "testBadaBing"
    ) AS x
WHERE rnk <= 1
ELSE
SELECT *
    FROM (
        SELECT *, RANK() OVER (PARTITION BY "pera_id" ORDER BY "surface" DESC) AS rnk
        FROM "testBadaBing"
    ) AS x
WHERE rnk <= 3
END

Do you have any ideas how to make this work? My knowledge of SQL is pretty limited. The code has to be SQLite/SpatiaLite compatible as I'm working in the QGIS environment. Thanks.

Answer 1

You can use boolean logic in the WHERE clause of the outer query:

SELECT *
FROM (
    SELECT t.*, 
        RANK() OVER (PARTITION BY "pera_id" ORDER BY "surface" DESC) AS rnk
    FROM "testBadaBing" t
) AS x
WHERE ("note" = 1 and rnk = 1) OR rnk <= 3