how to delete nth row of a table through sql query?
please say me
thanks,
Arunachalam
Jump to Postme
Jump to Postsorry, couldn't resist. Will need table schema to answer your question
me
sorry, couldn't resist. Will need table schema to answer your question
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
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.
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)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.