I know that the records in the table do not have such a thing as a sequence number, but the task is to write a SQL request to delete the i-th record, regardless of its id or any other parameters .. Are there ways to accomplish this? Upd1: For example, the third entry in the table in the original sort, i.e. in the order in which the records were added to the table. But the id may be different, not necessarily differing by one from neighboring records.

  • What is the i-th in a row? What sorting? - vp_arth
  • In any case, I would reduce the task to delete from where id = (select) - vp_arth
  • Yes, I understand, the simplest option, but in my case, id doesn't necessarily go in order, and the request should delete the third (fifth / tenth) record anyway, without looking at the data and id in particular .. - Glimpse Unthinkable
  • What is the difference, what kind of data is there, the task changes from "delete record # i" to "Select record # i", which can be done via offset / fetch or top / offset - vp_arth
  • Delete any entry. And then assure that she was the i-th one at the given location of the stars. - Alexander Petrov

3 answers 3

If id is unique, delete the 4th record in ascending order of id:

 delete from table where id=(select min(id) from table where id not in (SELECT top 3 id FROM table order by id)) ;
  • In the end, he settled on your version, but from the rest, he took only the upper entry and also deleted it on the basis of the equality id. Thank! - Glimpse Unthinkable

You can try this:

 DECLARE @I INT SELECT @I = 3 create table #MyTest (id int, name varchar(3)) insert #MyTest values (1,'A'), (3,'B'), (5,'C'), (7,'D'); WITH CTE (ID,NUM,RN) AS (SELECT ID,NAME,ROW_NUMBER() OVER (ORDER BY (ID)) FROM #MyTest) DELETE FROM CTE WHERE RN = @I SELECT * FROM #MyTest DROP TABLE #MyTest
  • one
    on ms-access think will work ??? - Serge Nazarenko
  • @SergeNazarenko label appeared after my reply ( - Nick Proskuryakov

You can use the DCOUNT function:

 DELETE FROM [MyTable] WHERE DCOUNT("[ID]","[MyTable]","[ID]<=" & [ID]) = <номер записи>

Although the author does not seem to need to be attached to the record ID, and indeed to any field. That is, just delete the i-th entry in the sample without any sorting conditions. I can not even suggest why this is necessary, especially without sorting, but then the answer is:

Delete any entry. And then assure that she was the i-th one at the given location of the stars. - Alexander Petrov

already seems reasonable. )

  • When it can be useful in practice - I can’t even imagine it myself, it's just that our university database teacher is still an inventor, with every laboratory more and more surprised) - Glimpse Unthinkable