SQLite. Как получить номер строки в выборке после сортировки?

[SQL*Plus]

Имеется таблица:

Код: Выделить всё

CREATE TABLE DEPT(
   DEPTNO INTEGER
 , DNAME  TEXT
 , LOC    TEXT
 , CONSTRAINT dept$p
      PRIMARY KEY(deptno)
) STRICT;

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

Её содержимое (без сортировки)

Код: Выделить всё

sqlite> SELECT * FROM dept;
+--------+------------+----------+
| DEPTNO |   DNAME    |   LOC    |
+--------+------------+----------+
| 10     | ACCOUNTING | NEW YORK |
| 20     | RESEARCH   | DALLAS   |
| 30     | SALES      | CHICAGO  |
| 40     | OPERATIONS | BOSTON   |
+--------+------------+----------+

При выполнении возрастающей сортировки по LOC получим:

Код: Выделить всё

sqlite> SELECT * FROM dept ORDER BY loc;

+--------+------------+----------+
| DEPTNO |   DNAME    |   LOC    |
+--------+------------+----------+
| 40     | OPERATIONS | BOSTON   | <--- some_rownum = 1
| 30     | SALES      | CHICAGO  | <--- some_rownum = 2
| 20     | RESEARCH   | DALLAS   | <--- some_rownum = 3
| 10     | ACCOUNTING | NEW YORK | <--- some_rownum = 4
+--------+------------+----------+

ВОПРОС:
Можно ли каким-то образом получить номера строк в такой выборке (some_rownum), чтобы их затем использовать?

Например так:

Код: Выделить всё

SELECT *
FROM (SELECT some_rownum, D.* FROM dept D ORDER BY loc)
WHERE some_rownum <= 2;

P. S. Такой псевдостолбец мне нужен, чтобы красиво выводить иерархии при использовании Recursive Subquery Factoring.

[SQL*Plus]

Нашел решение с использованием "оконной" функции ROW_NUMBER:

Код: Выделить всё

SELECT row_number() OVER() AS some_rownum, D.*
FROM (SELECT * FROM dept D ORDER BY loc) D;
+-------------+--------+------------+----------+
| some_rownum | DEPTNO |   DNAME    |   LOC    |
+-------------+--------+------------+----------+
| 1           | 40     | OPERATIONS | BOSTON   |
| 2           | 30     | SALES      | CHICAGO  |
| 3           | 20     | RESEARCH   | DALLAS   |
| 4           | 10     | ACCOUNTING | NEW YORK |
+-------------+--------+------------+----------+

Код: Выделить всё

SELECT some_rownum, deptno, dname, loc
FROM (SELECT row_number() OVER() AS some_rownum, D.*
      FROM (SELECT * FROM dept D ORDER BY loc) D)
WHERE some_rownum <= 2;
+-------------+--------+------------+---------+
| some_rownum | DEPTNO |   DNAME    |   LOC   |
+-------------+--------+------------+---------+
| 1           | 40     | OPERATIONS | BOSTON  |
| 2           | 30     | SALES      | CHICAGO |
+-------------+--------+------------+---------+

[SQL*Plus]

В Oracle Database можно обойтись псевдостолбцом ROWNUM:

Код: Выделить всё

SQL> SELECT some_rownum, deptno, dname, loc
  2  FROM (SELECT rownum AS some_rownum, D.*
  3        FROM (SELECT * FROM dept D ORDER BY loc) D)
  4  WHERE some_rownum = 2;

SOME_ROWNUM     DEPTNO DNAME          LOC          
----------- ---------- -------------- -------------
          2         30 SALES          CHICAGO    

А вот "фокус" ROW_NUMBER() с фразой OVER() без ORDER BY не проходит:

Код: Выделить всё

SQL> SELECT some_rownum, deptno, dname, loc
  2  FROM (SELECT row_number() OVER() AS some_rownum, D.*
  3        FROM (SELECT * FROM dept D ORDER BY loc) D)
  4  WHERE some_rownum = 2;

Error starting at line : 5 in command -
SELECT some_rownum, deptno, dname, loc
FROM (SELECT row_number() OVER() AS some_rownum, D.*
      FROM (SELECT * FROM dept D ORDER BY loc) D)
WHERE some_rownum = 2
Error at Command Line : 6 Column : 14
Error report -
SQL Error: ORA-30485: missing ORDER BY expression in the window specification