syntaxhelp.com Your online cheat sheet!

Generating a sequence number using ROW_NUMBER() - (SQL Server 2005 and above)

-- 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
*/

Generating a sequence number that resets after each category

-- 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(PARTITION BY Gender ORDER BY name) AS 'row_number'
FROM @t

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