how to check duplicate rows in tableof sql server
ca4nul wrote:hi all
i dont have primary key on one of my table. i want to check duplicate rows in table and further want to remove them
thanks
// Return all duplicate cities and how often they appear
// Works with ORACLE
Code:select city_name, count(city_name) as cnt
from areas
group by city_name
where cnt>1
// not all SQL dbms will support the reference to the count column cnt in the where clause.
// The following will return ALL rows with counter, but sorted by number of appearances
// Your duplicates will be at the top.
// Works with MYSQL
Code:select city_name, count(city_name) as cnt
from areas
group by city_name
order by cnt desc
// finally, no back reference to count column cnt at all-
// the following will work on all SQL dbms:
// Return all cities and how often they appear
Code:select city_name, count(city_name) as cnt
from areas
group by city_name
// version for Micrsoft's MSSQL Server
// make use of the HAVING clause
Code:select city_name
from areas
group by city_name
having count(*) > 1
