how to remove a multiple records for same zipcode keeping atleast one record for that zipcode in database table

how to remove a multiple 开发者_开发百科records for same zipcode keeping atleast one record for that zipcode in database table

id zipcode
1  38000 
2  38000 
3  38000 
4  38005
5  38005

i want table with two column with id and zipcode ... my final will be following

id zipcode
1  38000 
4  38005

How about

delete from myTable
where id not in (
    select Min( id )
    from myTable
    group by zipcode )

That lets you keep your lowest IDs, which is what you seemed to want.

To just select that resultset you would use a DISTINCT statement:

SELECT id, zipcode
FROM table
WHERE zipcode IN (SELECT DISTINCT zipcode FROM table)

To delete the other records and keep only one you usea subquery like so:

DELETE FROM table
WHERE id NOT IN 
(SELECT id
FROM table
WHERE zipcode IN (SELECT DISTINCT zipcode FROM table)
)

You can also accomplish this using a join if you perfer.

with cte as (
 select row_number() over (partitioned by zipcode order by id desc) as rn
 from table)
delete from cte
where rn > 1;

This has the advantage of correctly handling duplicates and offers tight control over what gets deleted and what gets kept.

Create temporary table with desired result:

select min(id), zipcode
into tmp_sometable
from sometable
group by zipcode

Remove the original table:

drop table sometable

Rename temporary table:

sp_rename 'tmp_sometable', 'sometable'; 

or something like:

delete from sometable
where id not in
 (
   select min(id)
   from sometable
   group by zipcode
 )

delete from table where id not in (select min(id) from table zipcode in(select distinct zipcode from table));

select distinct zipcode from table - would give the distinct zipcode in the table select min(id) from table zipcode in(select distinct zipcode from table) - would give the record with the min ID for each zip code delete from table where id not in (select min(id) from table zipcode in(select distinct zipcode from table)) - this would delete all the records in the table that are not there as a result of query 2

There's an easier way if you want the lowest ID number. I just tested this:

SELECT 
    min(ID),
    zipcode
FROM #zip
GROUP BY zipcode