Create Unique Id based on existing fields

I have the following table (Table does not have unique key id )

Last Name     First Name         DATE        Total-Chrg

Jaime               KRiSH           5/1/2015         -4150.66

Jaime               KRiSH           5/1/2015          1043.66

Jaime               KRiSH           5/1/2015          1043.66

Jaime               KRiSH           5/1/2015           4150.66

Jaime               KRiSH           5/3/2015           4150.66

Peter                Jason            5/1/2015           321.02

Peter                Jason            5/1/2015           321.02

Peter                Jason            5/23/2015         123.02

I want the results to be in following way

Uniq ID    Last Name    First Name          DATE         Total-Chrg

1                Jaime            KRiSH                 5/1/2015     -4150.66

2                Jaime            KRiSH                 5/1/2015     1043.66

2                Jaime            KRiSH                 5/1/2015     1043.66

3                Jaime            KRiSH                 5/1/2015      4150.66

4               Jaime             KRiSH                 5/3/2015      4150.66

5               Peter              Jason                  5/1/2015       321.02

5               Peter              Jason                   5/1/2015       321.02

7               Peter              Jason                   5/23/2015     123.02

May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values.  There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates). 


Thank You in advance. 

August 31st, 2015 8:40pm

DENSE_RANK() will work, as long as the primary goal you are trying to address is having the same unique id for your duplicates ( i would also change the name since they are not really unique) and having id's in exact order is secondary

The code below will match your desired result set:

CREATE TABLE #TEST (lastname varchar(50),FirstName varchar(50), date datetime, TotalChrg decimal(8,2))

insert #test
VALUES('Jaime',               'KRiSH',           '5/1/2015',         -4150.66),
('Jaime',               'KRiSH',           '5/1/2015',          1043.66),
('Jaime',               'KRiSH',           '5/1/2015',          1043.66),
('Jaime',               'KRiSH',           '5/1/2015',           4150.66),
('Jaime',               'KRiSH',           '5/3/2015',           4150.66),
('Peter',                'Jason',           '5/1/2015',           321.02),
('Peter',                'Jason ',           '5/1/2015',           321.02),
('Peter',                'Jason',            '5/23/2015',         123.02)



SELECT DENSE_RANK() OVER(ORDER BY Firstname DESC,LastName,date,TotalChrg), *
FROM #Test

HTH

Free Windows Admin Tool Kit Click here and download it now
August 31st, 2015 9:17pm

Why do you use DESC in ORDER BY FirstName ?
August 31st, 2015 11:42pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics