|
|
Posted by Hilarion on 10/20/05 18:01
> 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).
Navigation:
[Reply to this message]
|