So I have a table like this:
ID DESCRIPTION AGROUP
-----------------------
1 AN
2 FI
3 DOC 1 DOC
4 DOC 2 DOC
5 CO
6 RI RI
If the AGROUP column is not null, I want to select and group by that value, otherwise select the DESCRIPTION; in any case order by ID. The result I want is:
ID RESULT
-----------------------
1 AN
2 FI
3 DOC
5 CO
6 RI
I've tried creating a subquery first, with
SELECT
DISTINCT (NVL(AGROUP, DESCRIPTION)) AS DES
FROM
temp
GROUP BY NVL(AGROUP, DESCRIPTION)
And then adding the outer query:
SELECT
ID,
DES
FROM
(
SELECT DISTINCT (NVL(AGROUP, DESCRIPTION)) AS DES
FROM
temp
GROUP BY NVL(AGROUP, DESCRIPTION)
) T1
JOIN TEMP T2 ON (T1.DES = T2.AGROUP OR T1.DES = T2.DESCRIPTION)
ORDER BY ID
;
But then I receive DOC twice:
1 AN
2 FI
3 DOC
4 DOC
5 CO
6 RI
SQL makes my head explode! Thanks.
I think you just want group by:
select coalesce(AGROUP, DESCRIPTION) AS DES, min(id) as id
from temp
group by coalesce(AGROUP, DESCRIPTION);
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加