11 Years
Discussion Span
Last Post by campkev

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.



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.

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.


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)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.