Hi All,
This is my first mail in this categrory. And I hope some good response from u all. Actually I am searching some tricky queries(generally ask to write in interviews) along with the answer. Just like--
a) how to delete duplicate rows when there is no primary key field in a single query.

Plz forward, if any of you have some.

Thanks in advance.

Ummm... you need to write your own questions and then create, test, and understand your own answers. How can you expect an interviewee to answer "tricky" questions that you have no clue as to why the answers are what they are?

--Jeff Moden

Ok I will help you with your exam question.

a) if you have duplicate rows where every row is a duplicate you need to write a cursor and go through row by row comparing to the previous (assuming you selected in a good order) then delete the current row if it exactly matches every field of the previous row. Best way though is to add a unique value(primary key). Good luck with your homework.

Cursor? I don't think so....

--Jeff Moden

DELETE f
FROM (SELECT ROW_NUMBER() OVER PARTITION BY Col1 ORDER BY Col2 DESC) AS RecID FROM Table1) AS f
WHERE RecID > 1

... besides, the OP wasn't asking for a solution to a problem.... the OP was asking for trick questions that could be asked during an interview.

--Jeff Moden