SQL query for removing duplicate

This should return only one records per ID. The query below will return the MIN() value for both the city and state using a GROUP BY id field. Since you have more than one ID and you only want to return one value, then this will only return the first record that meets the requirement.

select ID, min(city) city, min(state) state
from yourTable
group by ID

See SQL Fiddle with Demo

So this works the following way

YourTable
ID   city    state
1    efef    dfdd
2    dwef    sdfsd
1    fdds    fsdfs

If we take the MIN() value of the city and state columns and GROUP BY id you result would be:

YourTable
ID   city    state
1    efef    dfdd
2    dwef    sdfsd

MIN() is going to return the lowest in the sequence. So since the city with the ID of 1 begins with e and e is before f then it will be selected. The GROUP BY combines all ID that match into a cluster.

Then if you wanted to exclude any record that had a duplicate id, then you could just change the query to:

select ID, min(city)  city, min(state)   state
from yourTable
group by ID
having min(city) = max(city)  
   and min(state) = max(state)

Edit:

You can also use:

select min(rowid) rowid, ID, city, state
from yourTable
group by ID

or:

select *
from yourTable t1
inner join
(
  select min(rowid) row_id, ID
  from yourTable
  group by ID
) t2
  on t1.rowid = t2.row_id
  and t1.id = t2.id

try this: if you are using sql server

Since there is no primary key for this table, it is difficult to identify which record to be deleted. So You could do the following

Step1:

;with cte as(
     select  id   , state,   city,
     row_number() over (partition by id order by (select 0))as row_num
     from <table>)
select * into #tmp
from cte where row_num=1

Step2:

truncate table <table>

Step3:

insert into <table>
select * from #tmp

Insert another rowID or equivalent unique column

select id,state,city
from Event e1
where e1.rowID in(
   select min(e2.rowID)
   from Event e2
   group by id)

delete * 
  from mytable 
 where rowid not in (
     select min(rowid) 
       from mytable
      group by column1, column2
     ) -- column1, column2 are the duplicate columns with which we want to group the rows

The above query is generic query to delete all duplicates. The inner query will return the single row for duplicate columns (column1, column2)

Edit: Please note that above query was tested in oracle. rowid is system generated id provided by oracle to all the rows and is unique. So if we use group by on some columns and there are three rows fulfilling the condition, oracle will add rowid as 1,2 and 3 to them.

I am sure all other databases have similar concept of adding a row number to the resultant data of a query