syntaxhelp.com Your online cheat sheet!

-- sample data
DECLARE @t TABLE(name VARCHAR(15), age INT, gender CHAR(1))
INSERT INTO @t(name, age, gender) SELECT 'Ted',12,'M'
INSERT INTO @t(name, age, gender) SELECT 'Joe',18,'M'
INSERT INTO @t(name, age, gender) SELECT 'Sue',16,'F'
INSERT INTO @t(name, age, gender) SELECT 'John',18,'M'
INSERT INTO @t(name, age, gender) SELECT 'Mary',19,'F'
INSERT INTO @t(name, age, gender) SELECT 'Doris',16,'F'

-- row_number() query
SELECT
    name, 
    age,
    gender,
    ROW_NUMBER()OVER(ORDER BY name) AS 'row_number'
FROM @t

/*
output: 
name            age         gender row_number
--------------- ----------- ------ -----------
Doris           16          F      1
Joe             18          M      2
John            18          M      3
Mary            19          F      4
Sue             16          F      5
Ted             12          M      6
*/

-- sample data
DECLARE @t TABLE(name VARCHAR(15), age INT, gender CHAR(1))
INSERT INTO @t(name, age, gender) SELECT 'Ted',12,'M'
INSERT INTO @t(name, age, gender) SELECT 'Joe',18,'M'
INSERT INTO @t(name, age, gender) SELECT 'Sue',16,'F'
INSERT INTO @t(name, age, gender) SELECT 'John',18,'M'
INSERT INTO @t(name, age, gender) SELECT 'Mary',19,'F'
INSERT INTO @t(name, age, gender) SELECT 'Doris',16,'F'


-- rank() query
SELECT 
    name, 
    age,
    gender,
    RANK()OVER(ORDER BY age) AS 'rank'
FROM @t

/*
output:
name            age         gender rank
--------------- ----------- ------ -------
Ted             12          M      1
Sue             16          F      2
Doris           16          F      2
John            18          M      4
Joe             18          M      4
Mary            19          F      6
*/

-- Sample data
DECLARE @t TABLE(name VARCHAR(15), age INT, gender CHAR(1))
INSERT INTO @t(name, age, gender) SELECT 'Ted',12,'M'
INSERT INTO @t(name, age, gender) SELECT 'Joe',18,'M'
INSERT INTO @t(name, age, gender) SELECT 'Sue',16,'F'
INSERT INTO @t(name, age, gender) SELECT 'John',18,'M'
INSERT INTO @t(name, age, gender) SELECT 'Mary',19,'F'
INSERT INTO @t(name, age, gender) SELECT 'Doris',16,'F'

-- dense_rank() query
SELECT 
    name,
    age,
    gender,
    DENSE_RANK()OVER(ORDER BY age) AS 'dense_rank'
FROM @t

/*
output:
name            age         gender dense_rank
--------------- ----------- ------ ------------
Ted             12          M      1
Sue             16          F      2
Doris           16          F      2
John            18          M      3
Joe             18          M      3
Mary            19          F      4
*/

-- sample data
DECLARE @t TABLE(name VARCHAR(15), age INT, gender CHAR(1))
INSERT INTO @t(name, age, gender) SELECT 'Ted',12,'M'
INSERT INTO @t(name, age, gender) SELECT 'Joe',18,'M'
INSERT INTO @t(name, age, gender) SELECT 'Sue',16,'F'
INSERT INTO @t(name, age, gender) SELECT 'John',18,'M'
INSERT INTO @t(name, age, gender) SELECT 'Mary',19,'F'
INSERT INTO @t(name, age, gender) SELECT 'Doris',16,'F'

-- ntile() query
SELECT
    name,
    age,
    gender,
    NTILE(3) OVER(ORDER BY age) AS 'ntile'
FROM @t
    
/*
output:
name            age         gender ntile
--------------- ----------- ------ -------
Ted             12          M      1
Sue             16          F      1
Doris           16          F      2
John            18          M      2
Joe             18          M      3
*/

 Cant find the page you are looking for?
 Help us to improve by adding the content that you are looking for.
 Leave a feedback
 We look forward to hear your comments and feedback.