SQL Server - 동일 순위 처리

동일 순위 처리하기

 

• 요구사항 : TOP N 추출 시 순위가 동일한 값들을 모두 포함하고 싶은 경우
• 해결방안 : WITH TIES 구문 사용

 

-- 주문 정보를 저장하는 Order 테이블 생성
CREATE TABLE OrderTable (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

-- 고객 정보를 저장하는 Customer 테이블 생성
CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(50)
);

-- 데이터 삽입
INSERT INTO Customer (CustomerID, CustomerName) VALUES (1, 'CustomerA');
INSERT INTO Customer (CustomerID, CustomerName) VALUES (2, 'CustomerB');
INSERT INTO Customer (CustomerID, CustomerName) VALUES (3, 'CustomerC');

INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (101, 1, '2022-01-01');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (102, 2, '2022-01-02');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (103, 1, '2022-01-03');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (104, 3, '2022-01-04');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (105, 1, '2022-01-05');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (106, 2, '2022-01-06');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (107, 1, '2022-01-07');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (108, 3, '2022-01-08');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (109, 1, '2022-01-09');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (110, 2, '2022-01-10');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (999, 2, '2023-01-10');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (989, 2, '2024-01-10');

/* -- 샘플 데이터 확인
SELECT *
FROM OrderTable a
JOIN Customer b 
ON a.CustomerID = b.CustomerID
*/


SELECT TOP 1 WITH TIES b.CustomerName AS [고객]
      , COUNT(a.OrderID) AS [주문건수합]
FROM OrderTable a
JOIN Customer b 
ON a.CustomerID = b.CustomerID
GROUP BY a.CustomerID, b.CustomerName
ORDER BY [주문건수합] DESC;