Re: Need help for constructing query

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