[MS-SQL] 그룹/개인별 순위 및 연속 번호

SELECT ROW_NUMBER() OVER(ORDER BY Value), * FROM (
SELECT '1' Value UNION ALL
SELECT '2' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '5' Value UNION ALL
SELECT '6' Value) SampleTable
    
SELECT RANK() OVER(ORDER BY Value), * FROM (
SELECT '1' Value UNION ALL
SELECT '2' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '5' Value UNION ALL
SELECT '6' Value) SampleTable
 
SELECT DENSE_RANK() OVER(ORDER BY Value), * FROM (
SELECT '1' Value UNION ALL
SELECT '2' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '3' Value UNION ALL
SELECT '5' Value UNION ALL
SELECT '6' Value) SampleTable

 

SELECT ROW_NUMBER() OVER(PARTITION BY [Group] ORDER BY Value), * FROM (
SELECT '1' Value, 'A' [Group] UNION ALL
SELECT '2' Value, 'A' [Group] UNION ALL
SELECT '3' Value, 'A' [Group] UNION ALL
SELECT '3' Value, 'B' [Group] UNION ALL
SELECT '5' Value, 'B' [Group] UNION ALL
SELECT '6' Value, 'B' [Group] ) SampleTable