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

Oops, obviously that should be "p.pos <= 3", but you get the idea...

On Tue, Mar 22, 2016 at 02:59:41PM -0700, Skylar Thompson wrote:
> 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@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-novice
>
> --
> -- Skylar Thompson (skylar2@u.washington.edu)
> -- Genome Sciences Department, System Administrator
> -- Foege Building S046, (206)-685-7354
> -- University of Washington School of Medicine
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

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