Re: Need help for constructing query

On 2011-03-27 rsmogura@softperience.eu (Radosław Smogura) wrote:

> Marco <netuse@lavabit.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