MSSQL에서 LIMIT 구문 흉내내기

DBMS를 쓰면서 솔찮이 많이 쓰곤 하는게 LIMIT 구문인데, 돈 없는 나는 맨날 mSQL이나 MySQL, pSQL, sqlite 같은 공짜만 쓰다보니 당연히 지원되는 기능인 줄 알았다. 근데 보니 LIMIT가 표준 SQL 구문이 아닐 뿐더러, 오히려 비싼 MS SQL에서는 LIMIT가 지원이 안되더라. 보니까 오라클도 안되는 거 같기도 하고... LIMIT 따윈 천민들이나 쓰는 기능인가? -,.- 뭐, 대신 MS SQL에선 TOP 구문으로 마치 LIMIT에 레코드 카운트만 지정하는 것 같은 기능을 지원하긴 하는데.... OFFSET 지정 안되는 LIMIT 기능이 의미가 있나?

뭐 어쨌건 다 살 방법은 있더라. 바로 WITH와 ROW_NUMBER()를 이용한 방법인데, WITH를 통해 SELECT한 결과로 임시 테이블을 만들고 거기서 ROW_NUMBER를 기준으로 레코드를 골라 내는 방법. 뭐 말로 설명하는 것 보다 예제로 설명하는 게 가장 쉬울 듯 하니 예제를 보자.

우선 아래와 같은 천민 DB에서 사용하던 SQL 코드가 있다고 할 때,

SELECT field1, field2 
FROM MYTABLE 
WHERE field3 <> ''
ORDER BY id 
LIMIT 10 OFFSET 10

이걸 MS SQL에서 쓸려면,

WITH TEMPTABLE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownum, field1, field2
    FROM MYTABLE 
    WHERE field3 <> ''
) 
SELECT field1, field2 
FROM TEMPTABLE 
WHERE rownum > 10 AND rownum <= 20
ORDER BY rownum

과 같이 사용하면 된다.

다만 주의할 점은 NESTED SELECT 로 생성되는 필드의 경우엔 ROW_NUMBER()의 OVER 조건에 사용할 수 없다는 점인데, 아래와 같은 SQL문은 MS SQL에서 동작하지 않는다.

WITH TEMPTABLE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY b_count) AS rownum,
         field1, b_count = (
            SELECT COUNT(*) 
            FROM MYTABLE_B 
            WHERE id = MYTABLE_A.field2
        ) 
    FROM MYTABLE_A
)
SELECT field1, b_count 
FROM TEMPTABLE

아마도 NESTED SELECT가 행해지는 시기와 상관 있을 것 같긴 한데 어쨌건 b_count가 unknown field라는 에러를 출력하게 된다. 대신 아래와 같이 JOIN 시킬 수 있다면 어쨌든 결과 값을 얻어 올 수는 있다.

WITH TEMPTABLE AS (
    SELECT ROW_NUMBER() OVER(ORDER BY b.count) AS rownum,
        a.field1 AS field1, b.count AS b_count
    FROM MYTABLE_A AS a
    LEFT JOIN MYTABLE_B AS b ON b.id=a.field2
)
SELECT field1, b_count 
FROM TEMPTABLE

아 그리고, MS SQL 2000 이전 버전에서는 아래와 같은 구문으로 얻어 오는 걸 지원했다고 하긴 하는데 어쨌건 요즘 흔히 사용하는 2005 이상 버전에서는 동작하지 않더라. 그리고 실제로 내가 2000버전 이전에서 직접 돌려보질 않았으니 2000 이전 버전에서 이게 확실하게 작동한다고 말할 수도 없으니 참고만 하길 바란다.

SELECT TOP 10 * FROM (
    SELECT TOP 20 
    FROM MYTABLE 
    ORDER BY id
) ORDER BY id DESC