Deletion

[arunachalamsa]

how to delete nth row of a table through sql query?

please say me

thanks,
Arunachalam

[campkev]

me

[campkev]

sorry, couldn't resist. Will need table schema to answer your question

[arunachalamsa]

what i meant in the question is delete based on row number..

if there are 10 row in a table, query should be such that i can delete 8th row through one query irrespective of the Table Attributes/schemas...

Am i question is wrong. do correct me if so.

Thanks,
Arun

[hollystyles]

If your schema is a good one the "8th" row will be uniquely identified by it's primary key so to delete a single row in a table you would use syntax like:

DELETE FROM <table> WHERE <primary key column> = <value>

You need to replace <table> with the table name obviously and <primary key column> with the column name and then <value> would be whatever the unique value is in primary key column of the 8th row of your table.

Example:
----------------------------------------
mytable |
----------------------------------------
pkID int | dbData varchar(50)|
----------------------------------------
1 |Hello |
2 |Goodbye |
.
.
.
8 |Go away |
----------------------------------------

so to delete the 8th row my TSQL would be:

DELETE FROM mytable WHERE pkID = 8

Simple really.

[campkev]

the way you want to do it, I don't know of a way without knowing the criteria you are sorting by and the table schema.

if you have a primary key and are sorting by that primary key,
example table customer with primary key customerid,
you could do this

delete from customer where customerid = (select top 1 c.customerid from (select Top 8 customerid from customer order by customerid asc) c order by c.customerid desc)