From: | Radosław Smogura <rsmogura(at)softperience(dot)eu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Marco <netuse(at)lavabit(dot)com> |
Subject: | Re: Need help for constructing query |
Date: | 2011-03-27 21:00:57 |
Message-ID: | 201103272300.58052.rsmogura@softperience.eu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
1st check (min < value and value < max) may be not needed, dependig what You
are looking for.
I didn't testd this
From | Date | Subject | |
---|---|---|---|
Next Message | Marco | 2011-03-27 21:41:18 | Re: Need help for constructing query |
Previous Message | Zheng Yang | 2011-03-27 12:51:21 | Re: foreign data wrappers |