Sduty/MS-SQL

순위생성

돌멘 2014. 1. 20. 15:41

1. 순위 생성

   SELECT ROW_NUMBER() OVER (PARTITION BY NM ORDER BY NUM) AS 'ROW_NUMBER'

                ,RANK() OVER (PARTITION BY NM ORDER BY NUM) AS 'RANK'

                ,DENSE_RANK() OVER (PARTITION BY NM ORDER BY NUM) AS 'DENSE_RANK'

      FROM TBL

 

==>      NM      NUM    ROW_NUMBER    RANK    DENSE_RANK

            A          10           1                    1               1

            B          10           2                    1               1

            C          8            3                    3               2

            D          7            4                    4               3