Posted by carlos on 10/20/05 14:32

On Thu, 20 Oct 2005 12:20:37 +0200, Stefan Rybacki <stefan.rybacki@gmx.net> 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)


Thank you very much !

[Back to original message]