[ DB / Oracle ] 정렬 (ORDER BY)

환경 : DBeaver

 

 정렬(ORDER BY)은 원하는 기준에 맞게 정렬을 시키는 것으로 조회 구문 마지막에 작성된다. (조건보다 뒤여야 함)

 

ORDER BY 컬럼명 ASC/DESC;

  - ASC : 오름차순

  - DESC : 내림차순

---

(참고) 이 글에서 사용되는 테이블 

CREATE TABLE vehicle (
  vehicle_id NUMBER PRIMARY KEY,	--차량 고유 숫자
  model_name VARCHAR2(30) NOT NULL,	--차량 모델
  vehicle_type VARCHAR2(15) CHECK(vehicle_type IN ('Sedan', 'SUV', 'Truck', 'Motorcycle')),
  price NUMBER,	--차량 종류
  manufacture_date DATE,	--차량 제조일자
  registration_expire_date DATE	--차량 등록 만료일
);

INSERT INTO vehicle VALUES(1, 'Toyota Camry', 'Sedan', 25000, '2021-01-15', '2023-01-17');
INSERT INTO vehicle VALUES(2, 'Honda CR-V', 'SUV', 30000, '2020-12-10', '2023-12-10');
INSERT INTO vehicle VALUES(3, 'Ford F-150', 'Truck', 40000, '2020-11-05', '2020-12-05');
INSERT INTO vehicle VALUES(4, 'Harley-Davidson Sportster', 'Motorcycle', 12000, '2022-02-20', '2023-02-20');
INSERT INTO vehicle VALUES(5, 'Tesla Model Y', 'SUV', 50000, '2021-03-30', '2024-03-30');
INSERT INTO vehicle VALUES(6, 'Chevrolet Silverado', 'Truck', 35000, '2020-08-15', '2023-08-15');
INSERT INTO vehicle VALUES(7, 'Yamaha MT-07', 'Motorcycle', 8000, '2021-05-01', '2023-02-01');
INSERT INTO vehicle VALUES(8, 'Nissan Altima', 'Sedan', 23000, '2020-07-10', '2023-07-15');
INSERT INTO vehicle VALUES(9, 'Ducati Panigale V4', 'Motorcycle', 25000, '2022-04-05', '2024-04-05');
INSERT INTO vehicle VALUES(10, 'Jeep Wrangler', 'SUV', 35000, '2020-10-01', '2023-11-30');

SELECT * FROM vehicle;

---

 

[ Primary Key를 오름차순 정렬 ]

SELECT * FROM vehicle ORDER BY vehicle_id ASC;

 

 

[ Primary Key를 내림차순 정렬 ]

SELECT * FROM vehicle ORDER BY vehicle_id DESC;

 

 

[ 차량을 이름(A-Z) 순으로 출력 ]

SELECT * FROM vehicle ORDER BY model_name ASC;
SELECT * FROM vehicle ORDER BY model_name ASC, vehicle_id ASC;	--기본키가 뒤집히지 않도록

 

 

[ 차량을 종류별로 가격이 비싼 순으로 출력 ]

SELECT * FROM vehicle ORDER BY vehicle_type ASC, price DESC;
SELECT * FROM vehicle ORDER BY vehicle_type ASC, price DESC, vehicle_id ASC;

 (+) 정렬을 중첩해서 사용할 수 있다. 

 

 

[ 연산 해보기 ] [ 만료일 - 제조일자 ]

SELECT * FROM vehicle ORDER BY (registration_expire_date - manufacture_date) ASC;
SELECT vehicle.*, (registration_expire_date - manufacture_date) 기한 FROM vehicle ORDER BY 기한 ASC, vehicle_id ASC;

 

 

 

 

 

 

개인 공부 기록용입니다:)