0

This question was asked for an interview, I still cant find an answer by googling it.

I have a table with two columns. Table doesn't have any primary key. I want to delete the duplicate rows. I can do it by alter table but they want a delete query.

Here is the table data.

empid name
1     rahul
2     rajeev
1     rahul
2     rajeev
2     rajeev
3     elias
4     amith
2     rajeev

The result should be be

1     rahul
2     rajeev
3     elias
4     amith
Wesley Murch
  • 101,186
  • 37
  • 194
  • 228
kiran
  • 33
  • 2

1 Answers1

2

As @Marc B says in the comments, this sounds like a trick question. But if it's not...

The only solution I know of (without being aware of a proper ranking function in mysql) is to somehow loop through each name and running the following query:

DELETE FROM tbl_name
WHERE name=@name
LIMIT 1

This wouldn't be one query so it wouldn't be a valid answer but I'm not sure of any other way to do it in mysql.

Another option would be to use SELECT DISTINCT into a temp table, TRUNCATE the original table, and INSERT the records back into it from a temp table. Again, this doesn't satisfy the requirements.

Joe Phillips
  • 49,743
  • 32
  • 103
  • 159
  • Heh, technically this is right - as long as the table only has 2 rows, and they are certainly duplicate values. – Wesley Murch May 24 '11 at 17:41
  • @Wesley Murch lol that's the only case this would be right... 3 rows and who knows what'll happen. – Rudu May 24 '11 at 17:43
  • He says he has a table with two rows and the rows are duplicates (so there's no way to discern between one or the other). This is an interview question so it's possible... if he clarifies I'll modify my answer. – Joe Phillips May 24 '11 at 17:45
  • I like this answer because (a) it's correct with the question as stated and (b) probably not what the interviewer was looking for. Stupid interview questions. – matt May 24 '11 at 17:49