FirebirdSQL logo
 CommentsProcedural SQL (PSQL) Statements 

ORDER BY and ROWS

The ORDER BY clause orders the set before the actual deletion takes place.It only makes sense in combination with ROWS, but is also valid without it.

The ROWS clause limits the number of rows being deleted.Integer literals or any integer expressions can be used for the arguments m and n.

If ROWS has one argument, m, the rows to be deleted will be limited to the first m rows.

Points to note

  • If m > the number of rows being processed, the entire set of rows is deleted

  • If m = 0, no rows are deleted

  • If m < 0, an error occurs and the deletion fails

If two arguments are used, m and n, ROWS limits the rows being deleted to rows from m to n inclusively.Both arguments are integers and start from 1.

Points to note

  • If m > the number of rows being processed, no rows are deleted

  • If m > 0 and <= the number of rows in the set and n is outside these values, rows from m to the end of the set are deleted

  • If m < 1 or n < 1, an error occurs and the deletion fails

  • If n = m - 1, no rows are deleted

  • If n < m -1, an error occurs and the deletion fails

Examples

Deleting the oldest purchase:

DELETE FROM Purchases
  ORDER BY date ROWS 1;

Deleting the highest custno(s):

DELETE FROM Sales
  ORDER BY custno DESC ROWS 1 to 10;

Deleting all sales, ORDER BY clause pointless:

DELETE FROM Sales
  ORDER BY custno DESC;

Deleting one record starting from the end, i.e. from Z…​:

DELETE FROM popgroups
  ORDER BY name DESC ROWS 1;

Deleting the five oldest groups:

DELETE FROM popgroups
  ORDER BY formed ROWS 5;

No sorting (ORDER BY) is specified so 8 found records, starting from the fifth one, will be deleted:

DELETE FROM popgroups
  ROWS 5 TO 12;

SKIP LOCKED

When the SKIP LOCKED clause is specified, records locked by a different transaction are skipped by the statement and are not deleted.

When a ROWS clause is specified, the “skip locked” check is performed after skipping the requested number of rows specified, and before counting the number of rows to delete.