Delete duplicate rows and keep unique rows in sql

Hi All,
I never understood the concept of cursor until today when I used to delete duplicate rows and keep unique rows in sql


DECLARE @A int
DECLARE @B int
DECLARE @C int
DECLARE @COUNTA int
DECLARE @COUNTB int
DECLARE @COUNTC int

DECLARE CUR_DELETE CURSOR FOR
SELECT A, B, C, COUNT(A), COUNT(B), COUNT(C) FROM G GROUP BY A, B, C HAVING COUNT(A) > 1 And COUNT(B) > 1 And COUNT(C) > 1

OPEN CUR_DELETE

FETCH NEXT FROM CUR_DELETE INTO @A, @B, @C, @COUNTA , @COUNTB, @COUNTC
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN

DELETE TOP(@COUNTA -1) FROM g WHERE A = @A
DELETE TOP(@COUNTB -1) FROM g WHERE A = @B
DELETE TOP(@COUNTC -1) FROM g WHERE A = @C

FETCH NEXT FROM CUR_DELETE INTO @A, @B, @C, @COUNTA , @COUNTB, @COUNTC
END

CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE


Enjoy!

Cannot define PRIMARY KEY constraint on nullable column in table

Hi All,
I used the below to alter table attributes



To add primary key
ALTER TABLE CSAT
ADD PRIMARY KEY ([Sl No])

While doing so, I got the below error
Cannot define PRIMARY KEY constraint on nullable column in table

So I used the below

ALTER TABLE CSAT ALTER COLUMN [Sl No] float NOT NULL


Viola

Hi All,
Tip in SSAS
To get the calculated field in SSAS, use 'float' as datatye in your SQL management studio

Hi All,
Today I ran a simple query to pull maximum value from three columns in a table and it is as below


select max(Name) As Name
from
(
Select MAX(A) as name from G Union All Select MAX(B)as name from G  Union All Select MAX(C)as name from G
) S

Enjoy!