IT Professionals

[PARAMASHIVAN]

App_engine anna and others

Has any one used the 'RANK' function in SQL Serever 2005/ 2008 or even in Oracle 8i to identify duplicate records ?

[app_engine]

Param,
By duplicate if you mean only some fields (and not the entire row), then using the analytic function RANK is a very standard practice in Oracle.

e.g. many people with the same first name / last name, like "John Anderson" but different emplid's or SSNs are "duplicate" in the "name-sense" and you can use the analytic function to pick one and get rid of others.

However, if the purpose is for deleting two "identical" rows (no unique constraint defined for the table), RANK won't work.

The earlier case is nicely explained here :
http://www.devx.com/getHelpOn/10Minu...ion/16597/1954

A SQL Solution: Deleting Duplicate Data with a Single SQL Statement Using RANK()
The Oracle 8i analytic function RANK() allows you to rank each item in a group.

In our case, we are using this function to assign dynamically sequential numbers in the group of duplicates sorted by the primary key. With RANK(), grouping is specified in the PARTITION BY clause and sort order for ranking is specified in the ORDER BY clause:

SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) SeqNumber
FROM Customers
ORDER BY LastName, FirstName;

Bingo! Now, values in the SeqNumber column, assigned by RANK(), allow you to separate all duplicate rows (SeqNumber > 1) from non-duplicates (SeqNumber = 1) and retrieve only those rows you want to delete:

SELECT ID, LastName, FirstName
FROM
(SELECT ID, LastName, FirstName,
RANK() OVER (PARTITION BY LastName,
FirstName ORDER BY ID) AS SeqNumber
FROM Customers)
WHERE SeqNumber > 1;

[kirukan]

App_engine anna and others

Has any one used the 'RANK' function in SQL Serever 2005/ 2008 or even in Oracle 8i to identify duplicate records ?

If you wanna delete duplicate rows then the technique we used earlier was creating a temp table identical to the table.insert distinct data into the temp table from the original and then move the same back to original.

insert into @temp select distinct col1,col2,col3 from mytable
delete mytable
insert into mystable select col1,col2,col3 from @temp

-
கிறுக்கன்

[PARAMASHIVAN]

test test test

[PARAMASHIVAN]

Thanks Kirukukan and App na , I will try that. BTW, I have question , how do you find the second highest value or 3rd highest value in a column, say you have vcolumn called salary, so ' Select Max(salary) from table' would return the highest salary, but how about the second or thir highest ?

[app_engine]

One technique in Oracle is by using rownum / order by

e.g.

select * from
(select rownum r, CR
from
(select distinct comprate CR
from ps_compensation
order by comprate)
)
where r = 2

[app_engine]

The above sql will get the 2nd lowest

For highest, your order by should add "desc".

[PARAMASHIVAN]

Thanks app Na

but would the above script work in SQL Server?

[app_engine]

It had been years since I used a sql server database. Possibly in 2002...been in all-oracle world since then.

Try it out

[PARAMASHIVAN]

Thanks App annEh