Sql Query Help

[cool_dude]

Hi DB people,

 

 

Kastha naa que ki solution cheppandi guys. 

 

 

I have the following table

 

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

 

 

Ante basically our table doesn't have any Primary key or unique Key values. So we need to create a unique ID. Marked in color are duplicates(But not duplicates as per the table records, so those will have the Unique value as same   2 and 5 in above scenario). 

 

In a nutshell, Unique ID create cheyyali vayyas, please help me I say....

 

Tnq in advance. 

 

 

[puli_keka]

unique key lekapothe we create composite key kada.. primary key on many columns ..LTT for sql batch    

[cool_dude]

unique key lekapothe we create composite key kada.. primary key on many columns ..LTT for sql batch    

Ante bhaiyya, the table is just a straight dump with no keys, above that Composite key toh cheddam anukunna, but due to duplicates em cheyyaledhu, It may be possible based on my  above required output, I will try more based on the inputs and queries from SQL thopus here....

[puli_keka]

Ante bhaiyya, the table is just a straight dump with no keys, above that Composite key toh cheddam anukunna, but due to duplicates em cheyyaledhu, It may be possible based on my  above required output, I will try more based on the inputs and queries from SQL thopus here....

check this out.. dense rank may solve your purpose http://stackoverflow.com/questions/7747327/sql-rank-versus-row-number

[loveindia]

here you go man...

 

nee output lo last record ki 7 ani pettav... but ideally in your case it will come out as 7 man...

 

DECLARE @Input TABLE
(
LastName VARCHAR(10),
FirstName VARCHAR(10),
[Date] DATE,
[Total-Chrg] DECIMAL(10,2)
)
 
INSERT INTO @Input(LastName, FirstName, [Date],[Total-Chrg])
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 LastName, FirstName, [Date], [Total-Chrg]) AS Rnum
FROM @Input