Select records with a Sequance number for each unique record

Transact-SQL
,
1

I have a table which i want to assign an ID for each unique record based on date, name & branch columns.

CREATE TABLE [TEST].[dbo].[uniqueID] (
[DATE] datetime,
[Name] nvarchar(50),
[Branch] nvarchar(50),
)

INSERT INTO [Test].[dbo].[uniqueID]
([DATE]
,[Name]
,[Branch]
)
VALUES
('2016-04-19','Apple','BranchA')
,('2016-04-19','Apple','BranchA')
,('2016-04-19','Apple','BranchB')
,('2016-04-20','Apple','BranchA')
,('2016-04-20','Orange','BranchA')
,('2016-04-20','Orange','BranchA')
,('2016-04-20','Apple','BranchB')

I tried with Rank, DENSE_RANK, ROW_NUMBER functions. But i could not get what i wanted.
SELECT *
,RANK() OVER(PARTITION BY [DATE],[Name],[Branch] ORDER BY [DATE],[Name],[Branch]) AS 'RANK'
,DENSE_RANK() OVER(PARTITION BY [DATE],[Name],[Branch] ORDER BY [DATE],[Name],[Branch]) AS 'DENSE_RANK'
,ROW_NUMBER()
OVER (PARTITION BY [DATE],[Name],[Branch] ORDER BY [DATE],[Name],[Branch]) AS Row
FROM [TEST].[dbo].[uniqueID]

I'm expecting an ID similar to below table.

Date Name Branch ID
'2016-04-19','Apple','BranchA', 1
'2016-04-19','Apple','BranchA', 1
'2016-04-19','Apple','BranchB', 2
'2016-04-20','Apple','BranchA', 3
'2016-04-20','Orange','BranchA', 4
'2016-04-20','Orange','BranchA', 4
'2016-04-20','Apple','BranchB', 5

Apreciate if someone can help on this..

2

If you want to add a Sequential Unique ID, then use NEWSEQUENTIALID(). I literally just learned this before signing up, lets see if I can help.

When you create your table, add an ID column like so with uniqueidentifier as the data type:

CREATE TABLE Test
(
ID uniqueidentifier DEFAULT NEWSEQUENTIALID(),
[Date] datetime,
Name nvarchar(50),
Branch nvarchar(50)
);

Then the INSERT INTO...

INSERT INTO Test
([Date], Name, Branch)
VALUES
('2016-04-19','Apple','BranchA');

Then go look, you'll see a unique ID int he first column per each entry. If you do not want them sequential, use: NEWID() in place of NEWSEQUENTIALID()

SELECT * FROM Test;

I hope I understood your question and was able to help. Let me know, I'm studying for the 70-461 myself.

3

Thanks for your inputs :smiley:, NEWID() creates a unique ID for each row regardless of any similarity in the values.

However i'm trying to get a ID considering the similarity in the values & increment the ID only if the row values are not similar.

4

Seems a bit "wordy", maybe there is a slicker way ...

SELECT
	P.*
	, [RN]
FROM	dbo.[uniqueID] AS P -- Parent
	JOIN
	(
		SELECT	[RN] = ROW_NUMBER() OVER(ORDER BY [DATE],[Name],[Branch])
			, [DATE],[Name],[Branch]
		FROM
		(
			SELECT DISTINCT [DATE],[Name],[Branch]
			FROM	dbo.[uniqueID]
		) AS X
	) AS C -- Child
		 ON C.[DATE] = P.[DATE]
		AND C.[Name] = P.[Name]
		AND C.[Branch] = P.[Branch]
ORDER BY P.[DATE], P.[Name], P.[Branch]
5

P.S. What if you insert (more) rows that match an existing row - do you need the ID from THAT row, or is this just an ID number WITHIN e.g. a report?

6

Thank you so much. This is exactly what i wanted.

I wanted to get the ID field into a report.