현재 테이블의 컬럼이
[KEY][TEMP][TIME]
이런식으로 있습니다.
[TIME] 컬럼은 입력시 '초' 단위까지 들어가기 때문에
간혹 2개 이상의 값이 들어갈때가 있습니다.
그래서 같은 시간 단위로 묶고, 그 중 높은 [TEMP] 값과 이에 해당되는 [KEY] 값을 출력하고자 합니다.
KEY | TEMP | TIME |
1 | 200 | 2019-05-22 09:00:00 |
2 | 190 | 2019-05-22 09:00:00 |
3 | 230 | 2019-05-22 09:00:01 |
4 | 240 | 2019-05-22 09:00:01 |
만약 이렇게 있다면
1/200/2019-05-22 09:00:00
4/240/2019-05-22 09:00:01
이렇게 출력하고 싶습니다.
현재
SELECT MAX(TMEP) TEMP, TIME
FROM TABLENAME GROUPBY TIME;
이런식으로 했는데 KEY 컬럼을 어떻게해야 원하는대로 출력을 할수있을까요?
물론 GROUP BY 키워드를 꼭 사용하지 않아도 됩니다.
답변 부탁드립니다.
WITH t AS ( select '1' key, '200' temp, '2019-05-22 09:00:00' time from dual union all select '2' key, '190' temp, '2019-05-22 09:00:00' time from dual union all select '3' key, '230' temp, '2019-05-22 09:00:01' time from dual union all select '4' key, '240' temp, '2019-05-22 09:00:01' time from dual ) select key, temp, time from ( select key, temp, time, row_number() over(partition by time order by temp) rk from t ) where time||rk in ( select time||max(rk) from ( select key, temp, time, row_number() over(partition by time order by key, temp) rk from t ) group by time ) order by key
WITH t AS
(
select '1' key, '200' temp, '2019-05-22 09:00:00' time from dual union all
select '2' key, '190' temp, '2019-05-22 09:00:00' time from dual union all
select '3' key, '230' temp, '2019-05-22 09:00:01' time from dual union all
select '4' key, '240' temp, '2019-05-22 09:00:01' time from dual
)
select
key, temp, time
from (select key, temp, time, row_number() over(partition by time order by temp desc ) rk from t)
where rk =1
캘린다님 참조