[mssql] 조회 순번 & 페이징 쿼리

ROW_NUMBER() OVER()

OVER() 에 ORDER BY 필수. (SELECT 1) 꼼수.

정렬한 순번이 필요하면 적절한 값 입력.

 

- 조회 순번

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownum
FROM myTable;

 

- 페이징 쿼리

DECLARE @PAGE_NO INT = 1
DECLARE @PAGE_ITEM_CNT INT = 10

SELECT rownum, col1, col2, col3, totalCnt
FROM (
	SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS rownum
		, col1, col2, col3
		, COUNT(*) OVER() AS totalCnt
	FROM myTable
) tb
WHERE rownum BETWEEN
	( (@PAGE_NO - 1) * @PAGE_ITEM_CNT ) + 1
    AND ( @PAGE_NO * @PAGE_ITEM_CNT )