Using a nested group by statement or sub query to filter this result sets
- by vivid-colours
This question is a continuation of Changing this query to group rows and filter out all rows apart from the one with smallest value but with an extra bit at the end....
I have the following results set:
275 72.87368055555555555555555555555555555556 foo 70
275 72.87390046296296296296296296296296296296 foo 90
113 77.06431712962962962962962962962962962963 foo 80
113 77.07185185185185185185185185185185185185 foo 60
that I got from this query:
SELECT id, (tbl2.date_modified - tbl1.date_submitted)/86400, some_value
FROM tbl1, tbl2, tbl3
WHERE tbl1.id = tbl2.fid
AND tbl1.id = tbl3.fid
Notice there are 4 rows with 2 ids. I wanted to filter the rows to get only the minimum number in the second column. This fixed it:
SELECT id, min((tbl2.date_modified - tbl1.date_submitted)/86400), max(some_value)
FROM tbl1, tbl2, tbl3
WHERE tbl1.id = tbl2.fid
AND tbl1.id = tbl3.fid
GROUP BY tbl1.id
so I got:
275 72.87368055555555555555555555555555555556 foo 70
113 77.06431712962962962962962962962962962963 foo 80
How can I change it to do the same but not include rows where the are other rows with some_value=90 ? I.e.
113 77.06431712962962962962962962962962962963 foo 80
I think I need some nested group or nested query ?!
Many thanks :).
