CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
HaakonFlaar
HaakonFlaar

Reputation: 397

How to iterate over distinct dates in a table

I have the following table:

id date gap
1 2020-01-01 3
2 2020-01-01 5
3 2020-01-01 8
4 2020-01-02 6
5 2020-01-02 10
6 2020-01-02 12

I want to iterate over the distinct dates and fetch the two rows with the highest "gap" value. Desired result:

id date gap
2 2020-01-01 5
3 2020-01-01 8
5 2020-01-02 10
6 2020-01-02 12

I have attempted the following

DO $$
DECLARE
    temprow record;
BEGIN
FOR temprow in
        SELECT DISTINCT date 
        FROM table
    LOOP
        SELECT *
        FROM table
        ORDER BY gap DESC
        LIMIT 2;
    END LOOP;
END;
$$;

Giving me the following error:

ERROR: query has no destination for result data.

Wrapping the last SELECT statement with a RETURN() gives:

ERROR: RETURN cannot have a parameter in function returning void

I am not sure if I even need a loop for this, so feel free to suggest a solution without a loop.

Upvotes: 1

Views: 844

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

You do not need a loop nor plpgsql for this.

select l.* from 
(
 select distinct "date" as dd from the_table
) t 
cross join lateral
(
 select * from the_table 
 where "date" = t.dd order by gap desc limit 2
) l
order by "date", gap;

Almost in plain language - for every distinct date dd fetch two rows.

Upvotes: 2

user330315
user330315

Reputation:

This can be done using window functions:

select id, "date", gap
from (
  select id, "date", gap, 
         dense_rank() over (partition by "date" order by gap desc) as rnk
  from the_table
) t
where rnk <= 2
order by "date", gap;

If there are two rows with the same (highest) gap on the same date, both will be returned. If you don't want that, use row_number() instead of dense_rank()

Online example

Upvotes: 3

Related Questions