how we delete 3rd row from one table

[rahul.boju]

please let me know the answer thanks in advance

[priyadarshikunal]

interview question??

[chulett]

It's a trick question!

[pandeesh]

In which database?

Please let us know, how you will select only the 3rd row from your table?

[chulett]

Technically, your first question is irrelevant.

[pandeesh]

Technically, your first question is irrelevant.

My guess is, if it's sql server database and there's a identity column(which is in sequence) , then we can make use of the below query:

Code: Select all

DELETE FROM table_name WHERE IDENTITY_COLUMN=3

That's why i have raised that question.

Thanks

[chulett]

Still, doesn't matter. Having an "identity column" (or any other surrogate value) with a 3 in it doesn't make it "the third row". It's not sequential media, after all.

That's not to say that based on very specific conditions and/or sort orders, you couldn't identify the third record in the result set. But in the table? No such thing. Like I said, a trick question. And they've already specified it is an Oracle database.

[ray.wurlod]

There's no such thing, for a user, as the third row in a table. The mechanism by which rows are stored in a table, are entirely the province of the database server.

[priyadarshikunal]

Third row in a table as such doesn't mean anything, however a question should be asked back like, on what basis you identify which is the third row. like based on date or some other sort key.. then rank() function comes in handy to get the third row.

But still there is no answer to the question asked in first post as it is.

[chandra.shekhar@tcs.com]

Let KEY_1 is ur PK(or any distinct column) and ABCD is your table,
then this might help

delete from ABCD where key_1 in
(select key_1 from (
select key_1 from ABCD
fetch first 3 rows only)
order by 1 desc
fetch first row only)

@everybody
For every Question there's an answer.

[pandeesh]

Chandra ,

based on some assumptions, you have prepared the query(PK,order by desc).
But the owner of this question din't provide all those details.

[chandra.shekhar@tcs.com]

@pandeesh
I know what you are trying to say, but the bottomline is that the query works.

[ray.wurlod]

... but does not necessarily delete the third row from the table.

[chulett]

But still there is no answer to the question asked in first post as it is.

Ah... but there is, and it has been answered.

[priyadarshikunal]

seems tit for tat kind of solution... you didn't give me the condition, i deleted the 3rd row encountered. You can't blame me if you didn't wanted to delete that record.