Re: Tricky SQL problem - retrieve information_schema info and make use of it.

Hi Paul,

If I'm understanding what you're trying to do, I think a window function
will be your friend:

http://www.postgresql.org/docs/9.3/static/tutorial-window.html

Something like this should do the trick:

SELECT p.domain_id,p.d_date FROM (SELECT
domain_id,
d_date,
rank() OVER (PARTITION BY domain_id ORDER BY d_date DESC) AS pos
FROM dom_test) AS p
WHERE
p.pos < 3;

On Tue, Mar 22, 2016 at 09:51:57PM +0000, Paul Linehan wrote:
> 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');
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

--
-- Skylar Thompson (skylar2(at)u(dot)washington(dot)edu)
-- Genome Sciences Department, System Administrator
-- Foege Building S046, (206)-685-7354
-- University of Washington School of Medicine