Posted by Stefan Rybacki on 10/20/05 13:20

carlos@gkpwdun.com wrote:
> I need some help with a query.
>
> I need to write a query that selects the largest
> status per group, but the status must be smaller
> or equal than 160.
>
> key group status
>
> K03 G12 110
> K06 G12 140
> K07 G12 150
> K08 G12 160 -- Should be selected.
> K11 G12 170
>
> K12 G14 110
> K13 G14 150 -- Should be selected.
> K14 G14 170
>
> K16 G15 110
> K17 G15 130
> K18 G15 140 -- Should be selected.
> K19 G15 170
> K20 G15 180
>
> I fail to see how I can get '<= 160' and GROUP BY
> to work together in this case.
>
> (How) can this be done ?


SELECT * FROM table t1 WHERE status=(SELECT MAX(t2.status) FROM table t2 WHERE
t2.status<=160 AND t2.group=t1.group)

Regards
Stefan

[Back to original message]