From: | Marco <netuse(at)lavabit(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Need help for constructing query |
Date: | 2011-03-27 21:41:18 |
Message-ID: | 4d8fb1b2$0$6992$9b4e6d93@newsspool4.arcor-online.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2011-03-27 rsmogura(at)softperience(dot)eu (Radosław Smogura) wrote:
> Marco <netuse(at)lavabit(dot)com> Friday 25 March 2011 14:25:47
> > Hi,
> >
> > I have a table like this:
> >
> > id date min max value
> > 1 2011-03-25 20 30 17
> > 3 2011-03-21 40 55 43
> > 3 2011-03-23 40 55 52
> > 2 2011-02-25 5 2
> > 4 2011-03-15 74
> > 4 2011-03-25 128
> > 1 2011-03-22 20 30 24
> >
> > I'm looking for a query that outputs the last rows (highest date) per id
> > where the value is between min and max. I already have problems displaying
> > the last rows per id. Something like
> >
> > select id, max(date) from mytable group by id;
> >
> > gives just the id and the date, not the other values. I think of doing
> > this in two steps:
> >
> > 1) Display the rows with the highest date per id. That gives as many rows
> > as ids exist.
> > 2) Remove the rows that do not match ( value<max and value>min )
> >
> >
> > Marco
> SELECT * FROM where (min < value and value < max) and (id, date) in (
> SELECT id, max(date) WHERE (min < value and value < max) group by
> id)
This seems to do the task. Thanks you for the snippet!
Marco
From | Date | Subject | |
---|---|---|---|
Next Message | AI Rumman | 2011-03-28 05:26:31 | index bloat query understand |
Previous Message | Radosław Smogura | 2011-03-27 21:00:57 | Re: Need help for constructing query |