I'm trying to delete all the rows that are behind the top 5 rows.
CREATE PROCEDURE DeleteOtherNotes
AS
DELETE FROM notes
WHERE notes_id NOT IN
(SELECT notes_id
FROM
(SELECT notes_id FROM notes ORDER BY notes_id DESC)
foo);
In which, of course, notes is the table, and notes_id is the auto numbered id of that table.
Problem is, i'll get this message:
Msg 1033, Level 15, State 1, Procedure DeleteOtherAantekeningen, Line 8<br />
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
What can i do about this?