SQL вернуть rownum определенной строки? (используя Oracle db)

Question

В Oracle 10g у меня есть этот SQL:

select dog.id as dogId from CANINES dog order by dog.codename asc

который возвращает:

id
--
204
203
206
923

Я хочу расширить этот запрос, чтобы определить oracle rownum dog.id в этом наборе результатов.

я пытался

select rownum from 
(select dog.id as dogId from CANINES dog order by dog.codename asc) 
where dog.id=206

Но это не очень хорошо работает (оно возвращает 1 независимо от того, на какой dog.id я подхожу). Я ожидал, чтобы вернуться 3.

Спасибо за вашу помощь!

---

Заметки

http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

Я уверен, что мне не нужно использовать rowid

Answer 1

Я подозреваю, что вы хотите использовать аналитическую функцию (RANK, DENSE_RANK, или же ROW_NUMBER), т.е.

SELECT rnk
  FROM (select dog.id as dogId,
               ROW_NUMBER() OVER( ORDER BY dog.codename ASC ) rnk
          from CANINES dog )
 WHERE dogId = 206

Если столбец ID в таблице CANINES не был уникальным, RANK, DENSE_RANK, а также ROW_NUMBER) будет относиться к связи по-другому.

Если вы хотите сделать это исключительно с ROWNUM,

SELECT rn
  FROM (
        SELECT dogId, rownum rn
          FROM (select dog.id as dogId
                  from CANINES dog 
                 order by dog.codename ASC) inner
       ) middle
 WHERE dogId = 206

Answer 2

Если вам нужен уникальный идентификатор каждой строки в таблице, вам нужен ROWID, а не ROWNUM.

ROWNUM - это псевдостолбец, который может меняться каждый раз, когда исполняется бит SQL (он обрабатывается во время запроса)

Answer 3

Для этого было бы лучше изменить таблицу и добавить последовательность. Это может стать липким, если вы собираетесь удалять строки. Где, возможно, лучше будет использовать столбец состояния или мотив начала и конца даты, чтобы решить, какие строки активны и должны быть возвращены.

Answer 4

Посмотрите, работает ли это для вас:

Ответ

SELECT dog1.DogID, dog1.DogName, COUNT(*) AS rownumber
FROM #ids dog1, #ids dog2
WHERE dog2.DogName <= dog1.DogName
GROUP BY dog1.DogID, dog1.DogName
ORDER BY dog1.DogName

Результаты

DogID       DogName    rownumber
----------- ---------- -----------
204         Dog 1      1
203         Dog 2      2
206         Dog 3      3
923         Dog 4      4

DDL

CREATE TABLE #ids (DogID int NOT NULL PRIMARY KEY, DogName varchar(10) NOT NULL)
INSERT INTO #ids (DogID, DogName) VALUES (204, 'Dog 1')
INSERT INTO #ids (DogID, DogName) VALUES (203, 'Dog 2')
INSERT INTO #ids (DogID, DogName) VALUES (206, 'Dog 3')
INSERT INTO #ids (DogID, DogName) VALUES (923, 'Dog 4')