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 ?

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 WHE
RE
t2.status<=160 AND t2.group=t1.group)

Regards
Stefan

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

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

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