Posted by carlos on 10/21/05 11:07

On Thu, 20 Oct 2005 17:01:14 +0200, "Hilarion" <hilarion@SPAM.op.SMIECI.pl> wrote:

>> 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 ?
>
>
>If you do not need the "key" value for that almost
>largest "status" in "group", then this should do:
>
>SELECT group, MAX( status ) AS max_status
>FROM table_name
>WHERE status <= 160
>GROUP BY group
>ORDER BY group
>
>If you do need that "key" value, then you can use Stefan's
>method or join the above query with the oryginal table
>like this:


I do need the 'key'. By the way, the data that I presented here was
just some 'sample'-data, my real table columns are not called 'key',
'group' and 'status'. :-)


>SELECT t1.key, t1.group, t1.status, t2.avg_status
>FROM table_name AS t1 INNER JOIN (
> SELECT group, MAX( status ) AS max_status, AVG( status ) AS avg_status
> FROM table_name
> WHERE status <= 160
> GROUP BY group
>) AS t2 ON t1.group = t2.group AND t1.status = t2.max_status
>ORDER BY t1.group, t1.key
>
>This will (as Stefan's method) return more than one
>record for a group which has more than one entry with
>same value for status (and that value is the group maximum).
>
>The method with join should be slightly faster than that
>with subquery (Stefan's method) and it also allows
>outputing some additional grouped data (as average status
>in example above).


Thank you very much for the insights !



>Hilarion
>
>PS.: You should not use "group", "key" and in some cases "status"
> name as column (or table or view etc.) names because those
> are reserved keywords in most SQL dialects. If you have to
> use them, then you should quote them using apropriate
> quoting method (check your DBMS SQL manual, because quoting
> methods differ between different DBMSes, for example you
> use double-quotes in Oracle SQL or PL/SQL, "`" sign in MySQL
> and square brackets in MS Transact-SQL).

Yeah, I known. :-)
As I said, this was just an example, but again, thanks for the insight !

Carlos.

[Back to original message]