Common ranking functions of T-SQL

Outline:

-- Distinct remove duplicate items

-- With ties

-- Newid () new ID

-- Row_number () row number

-- Rank () ranking (drop one ranking)

-- Dense_rank () ranking (no lower than one ranking)

-- Ntile (page number) Page

-- Stored procedure using ntile (page number) Paging

T-SQLCodeAs follows:

Use S100801a
Go

Select * FromScore

-- remove duplicate items
select distinct (score) from score

-- retained duplicate items (note: with ties and top... order by)
select top 1 with ties score from score
order by score DESC

--Newid ()
Select Newid()As 'New ID',* FromScore

-- Generate a 'line number 'based on the descending order of the 'score' field'
Select Row_number () Over ( Order   By Score Desc ) As   ' Row number ' ,
Stuid As   ' Student ID ' , Score As   ' Score '   From Score

-- Based on the 'row number 'rownum of the temporary table temp_score, the records with the 'row number' between 1 and 20 are obtained.
With Temp_score As
(
Select Row_number () Over ( Order   By Score Desc ) As Rownum,
Stuid, score From Score
)
Select Rownum As   ' Row number ' , Stuid As   ' Student ID ' , Score As   ' Score '  
From Temp_score Where Rownum Between   1   And   20 ;

-- rank by score. (If the scores are the same, the next ranking will drop by one .)
select stuid, score,
rank () over ( order by score DESC ) as ' ranking '
from score

-- rank by score. (If the scores are the same, the next ranking will not drop .)
select stuid, score,
dense_rank () over ( order by score DESC ) as ' ranking '
from score

-- Ntile: Used to paging (or grouping) the entire table ),
-- And specify the page on which each record belongs.
Select Stuid, score,
Ntile ( 3 ) Over ( Order   By Score Desc ) As   ' Page number '
From Score
Order   By Score Desc

--==========================================
--Stored procedure using ntile (page number) Paging
--==========================================

--Delete stored procedure
Drop ProcedureUp_page
Go

-- Create a stored procedure
Create   Procedure Up_page
@ Pagecount   Int , -- Defines the number of data displayed on each page
@ Currentpage   Int -- Select the data page to display
As
Select   *   From (
Select Ntile (( Select   Count ( * ) / @ Pagecount   From Score ))
Over ( Order   By Stuid) As Page, *   From Score
) Where Page = @ Currentpage
Go

--View results
ExecUp_page2,3
--Indicates that two data entries are displayed on each page, and the current page displays 3rd.

Reference Source: ranking function (TRANSACT-SQL)

MS-help: // Ms. sqlcc. V9/ms. sqlsvr. v9.zh-CHS/tsqlref9/html/e7f917ba-bf4a-4fe0-b342-a91bcf88a71b.htm