Re: Tricky SQL problem - retrieve information_schema info and make use of it. - Mailing list pgsql-novice

Hi all,

I have an easy problem - just can't get my head around it.

I have a domain id and a date - what I want is the 3 highest
dates by domain id.

I prepared a complete schema (see below). The result I want is


1, 2016-02-24
1, 2016-02-25
1, 2016-02-26
2, 2016-02-25
2, 2016-02-26
2, 2016-02-27
3, 2016-03-27
3, 2016-03-28
3, 2016-03-29


I've tried a couple of things, but my brain isn't working tonight :-)

select max(distinct(domain_id)), max(d_date)
from dom_test
group by domain_id, d_date
limit 3;

select domain_id, max(d_date) from
(
 select distinct(domain_id), d_date from dom_test
 group by domain_id
) tab;


My schema.

create table dom_test(domain_id int, d_date date);

insert into dom_test values(1, '2016-02-23');
insert into dom_test values(1, '2016-02-24');
insert into dom_test values(1, '2016-02-25');
insert into dom_test values(1, '2016-02-26');
insert into dom_test values(2, '2016-02-23');
insert into dom_test values(2, '2016-02-24');
insert into dom_test values(2, '2016-02-25');
insert into dom_test values(2, '2016-02-26');
insert into dom_test values(2, '2016-02-27');
insert into dom_test values(3, '2016-02-23');
insert into dom_test values(3, '2016-02-24');
insert into dom_test values(3, '2016-02-25');
insert into dom_test values(3, '2016-02-26');
insert into dom_test values(3, '2016-03-27');
insert into dom_test values(3, '2016-03-28');
insert into dom_test values(3, '2016-03-29');