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
Improve this question
10
  • Can you give an example of the two rows? Commented May 24, 2011 at 17:39
  • 1
    @kiran: trick question. Given there's only two rows, then deleting either of the rows (leaving 1 row) eliminates the duplicate. Commented May 24, 2011 at 17:44
  • @Joe Philips :empid name ,1 rahul,2 rajeev, 1 rahul , 2 rajeev, 2 rajeev, 3 elias, 4 amith, 2 rajeev and the result will be 1 rahul,2 rajeev, 3 elias, 4 amith -> i cant align this sorry Commented May 24, 2011 at 17:45
  • 1
    @kiran Edit your original question so the table can be formatted properly Commented May 24, 2011 at 17:46
  • @kiran I have edited your post, please review and make sure this is correct. Commented May 24, 2011 at 17:55

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Heh, technically this is right - as long as the table only has 2 rows, and they are certainly duplicate values.
@Wesley Murch lol that's the only case this would be right... 3 rows and who knows what'll happen.
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.
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.