Hi

I have the table like below I want to gave Rankings to GUID Column. GUID col has Duplicate rows I am not getting unique ranking.

Can you please help me thanks in advance.

How could you even managed to get duplicate GUID?

Use Dense_Rank()

Why aren't you selecting DISTINCT rows?

Hello ,

Cursor does insert rows into above table. when ever  cusrosr fire everytime appering same GUID.

Thanks

• Edited by SQLCHERU 19 hours 52 minutes ago

Hello ,

Cursor does insert rows into above table. when ever  cusrosr fire everytime appering same GUID.

Thanks

 It is highly unlikely to have a duplicate GUID/GUID collision. Could you please share your code here to see whats going on. Also, please share your table structure and some sample data in DDL and DML format.

Hello ,

Cursor does insert rows into above table. when ever  cusrosr fire everytime appering same GUID.

Thanks

• Edited by SQLCHERU Friday, May 22, 2015 11:14 AM

Here is an answer, demonstrating how to use DENSE_RANK, and also showing how ROW_NUMBER is possibly part of what you want, showing two ways to use Dense_Rank (with and without partition by), and showing how DENSE_RANK and ROW_NUMBER differ in behavior

As others have pointed out, it's unusual to have duplicate GUIDs, so I'm just assuming the GUIDs represent a product identifier or something else, and the fact that it's a GUID is just a byproduct of how you chose to track indentifiers.

with DEMODATA as 
(Select * From (  Values 
	('guid 1', 11), 
	('guid 1', 11), 
	('guid 1', 11), 
	('guid 1', 11), 
	('guid 2', 11), 
	('guid 2', 11), 
	('guid 2', 11), 
	('guid 3', 11), 
	('guid 3', 11), 
	('guid 4', 22) 
) as VT(Fake_GUID, Fake_Quantity)
)
Select *
	, dense_rank() over(order by Fake_GUID) as GUID_Rank
	, dense_rank() over(partition by Fake_GUID order by Fake_Quantity) as Qty_Rank_notice_the_ties
	, row_number() over(partition by Fake_GUID order by Fake_Quantity) as Qty_ROW_NUMBER_notice_there_are_no_ties
from DEMODATA


/* RESULTS
Fake_GUID	Fake_Quantity	GUID_Rank	Qty_Rank_notice_the_ties	Qty_Rank_notice_there_are_no_ties
guid 1	11	1	1	1
guid 1	11	1	1	2
guid 1	11	1	1	3
guid 1	11	1	1	4
guid 2	11	2	1	1
guid 2	11	2	1	2
guid 2	11	2	1	3
guid 3	11	3	1	1
guid 3	11	3	1	2
guid 4	22	4	1	1
*/

Here is an answer, demonstrating how to use DENSE_RANK, and also showing how ROW_NUMBER is possibly part of what you want, showing two ways to use Dense_Rank (with and without partition by), and showing how DENSE_RANK and ROW_NUMBER differ in behavior

As others have pointed out, it's unusual to have duplicate GUIDs, so I'm just assuming the GUIDs represent a product identifier or something else, and the fact that it's a GUID is just a byproduct of how you chose to track indentifiers.

with DEMODATA as 
(Select * From (  Values 
	('guid 1', 11), 
	('guid 1', 11), 
	('guid 1', 11), 
	('guid 1', 11), 
	('guid 2', 11), 
	('guid 2', 11), 
	('guid 2', 11), 
	('guid 3', 11), 
	('guid 3', 11), 
	('guid 4', 22) 
) as VT(Fake_GUID, Fake_Quantity)
)
Select *
	, dense_rank() over(order by Fake_GUID) as GUID_Rank
	, dense_rank() over(partition by Fake_GUID order by Fake_Quantity) as Qty_Rank_notice_the_ties
	, row_number() over(partition by Fake_GUID order by Fake_Quantity) as Qty_ROW_NUMBER_notice_there_are_no_ties
from DEMODATA


/* RESULTS
Fake_GUID	Fake_Quantity	GUID_Rank	Qty_Rank_notice_the_ties	Qty_Rank_notice_there_are_no_ties
guid 1	11	1	1	1
guid 1	11	1	1	2
guid 1	11	1	1	3
guid 1	11	1	1	4
guid 2	11	2	1	1
guid 2	11	2	1	2
guid 2	11	2	1	3
guid 3	11	3	1	1
guid 3	11	3	1	2
guid 4	22	4	1	1
*/

• Proposed as answer by Kalman TothModerator 15 hours 26 minutes ago