database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41963 게시물 읽기
No. 41963
조건에 따른 order by 설정
작성자
차상환
작성일
2023-06-16 21:43ⓒ
2023-06-16 22:06ⓜ
조회수
2,831

안녕하세요. sql 어떻게 해야할지 ... 부탁드려요.


아래의 데이터에 No로 정렬되어 있는데


blue : 3 / red : 2 순으로 정렬하고 싶습니다.


 

GP No 원하는 순서order by
blue k1 1
blue k2 2
blue k3 3
blue k4 6
blue k5 7
blue k6 8
blue k7 11
blue k8 12
blue k9 13
blue k10 16
red k11 4
red k12 5
red k13 9
red k14 10
red k15 14
red k16 15
red k17 17


WITH DT AS (

  SELECT 'blue' AS GP, 'k1'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k2'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k3'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k4'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k5'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k6'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k7'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k8'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k9'  AS NO FROM DUAL UNION ALL

  SELECT 'blue' AS GP, 'k10' AS NO FROM DUAL UNION ALL

  SELECT 'red'  AS GP, 'k11' AS NO FROM DUAL UNION ALL

  SELECT 'red'  AS GP, 'k12' AS NO FROM DUAL UNION ALL

  SELECT 'red'  AS GP, 'k13' AS NO FROM DUAL UNION ALL

  SELECT 'red'  AS GP, 'k14' AS NO FROM DUAL UNION ALL

  SELECT 'red'  AS GP, 'k15' AS NO FROM DUAL UNION ALL

  SELECT 'red'  AS GP, 'k16' AS NO FROM DUAL UNION ALL

  SELECT 'red'  AS GP, 'k17' AS NO FROM DUAL 

)

SELECT *

  FROM DT



 

이 글에 대한 댓글이 총 3건 있습니다.

https://mjn5027.tistory.com/107

lucky님이 2023-06-16 23:48에 작성한 댓글입니다. Edit
WITH dt AS
(
SELECT 'blue' gp, 'k1' no FROM dual
UNION ALL SELECT 'blue', 'k2'  FROM dual
UNION ALL SELECT 'blue', 'k3'  FROM dual
UNION ALL SELECT 'blue', 'k4'  FROM dual
UNION ALL SELECT 'blue', 'k5'  FROM dual
UNION ALL SELECT 'blue', 'k6'  FROM dual
UNION ALL SELECT 'blue', 'k7'  FROM dual
UNION ALL SELECT 'blue', 'k8'  FROM dual
UNION ALL SELECT 'blue', 'k9'  FROM dual
UNION ALL SELECT 'blue', 'k10' FROM dual
UNION ALL SELECT 'red' , 'k11' FROM dual
UNION ALL SELECT 'red' , 'k12' FROM dual
UNION ALL SELECT 'red' , 'k13' FROM dual
UNION ALL SELECT 'red' , 'k14' FROM dual
UNION ALL SELECT 'red' , 'k15' FROM dual
UNION ALL SELECT 'red' , 'k16' FROM dual
UNION ALL SELECT 'red' , 'k17' FROM dual
)
SELECT gp, no
     , ROW_NUMBER() OVER(ORDER BY CEIL(rn / x), gp, rn) rn
  FROM (SELECT gp, no
             , ROW_NUMBER() OVER(PARTITION BY gp ORDER BY LENGTH(no), no) rn
             , DECODE(gp, 'blue', 3, 2) x
          FROM dt
        )
;
마농(manon94)님이 2023-06-19 13:10에 작성한 댓글입니다.

감사합니다.

차상환님이 2023-06-25 21:46에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41967쿼리 질문드립니다! [1]
뽕구파파
2023-08-08
398
41966SqL 도움 요청입니다. [2]
김진수
2023-07-25
655
41964스칼라 서브쿼리를 아우터 조인으로 [1]
유경일
2023-07-12
913
41963조건에 따른 order by 설정 [3]
차상환
2023-06-16
2831
41962GLOBAL TEMPORARY TABLE COMMIT 옵션 변경가능 한지요 ? [1]
이규동
2023-06-15
2759
41961NLS_DATE_FORMAT변경관련 질문입니다.
우태열
2023-05-13
4893
41948ORACLE DB SQL 쿼리 튜닝-8번째[DB 튜닝]
조장복
2023-03-21
5900
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.017초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다