CODEWITHSUNDEEP
sqlsql-serverfunction
user2164702
user2164702

Reputation: 33

SQL return incremented number

Hi I want to create a SQL function to return either 1 if a name in a column is unique, otherwise if that name is not unique then return an increment of 1. Here is what it should look lile:

NAME | Number
A    | 1
B    | 1
C    | 1
C    | 2
C    | 3
D    | 1
E    | 1

The problem I am having is getting the number to increment.

Upvotes: 3

Views: 170

Answers (3)

user330315
user330315

Reputation:

ANSI SQL:

select name,
       row_Number() over (partition by name order by name) as number
from your_table

Upvotes: 7

Paul Coghill
Paul Coghill

Reputation: 677

If it's SQL Server, have a look here, at the Section C: Using ROW_NUMBER() with PARTITION:

http://msdn.microsoft.com/en-us/library/ms186734.aspx

SELECT 
name
, ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) number
FROM tbl

Upvotes: 2

MarkHickin
MarkHickin

Reputation: 46

CREATE TABLE #TEST (NAME CHAR(1))

INSERT INTO #TEST 
SELECT 'A'
UNION ALL
SELECT 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'D'

SELECT NAME, ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME) AS Number
FROM #TEST

Upvotes: 2

Related Questions