Thread: Need help for constructing query
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
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) 1st check (min < value and value < max) may be not needed, dependig what You are looking for. I didn't testd this
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