SQL Query GROUP BY and "smaller than"

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 ?

Re: SQL Query GROUP BY and "smaller than"

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

Re: SQL Query GROUP BY and "smaller than"

On Thu, 20 Oct 2005 12:20:37 +0200, Stefan Rybacki 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 !

Re: SQL Query GROUP BY and "smaller than"

> 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:

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).


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).

Re: SQL Query GROUP BY and "smaller than"

On Thu, 20 Oct 2005 17:01:14 +0200, "Hilarion" 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.