SQL to return the rownum of a specific row? (using Oracle db)

Question

In Oracle 10g, I have this SQL:

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

which returns:

id
--
204
203
206
923

I want to extend this query to determine the oracle rownum of a dog.id in this resultset.

I have tried

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

But this does not work out very well (it returns 1 no matter which dog.id I match on). I was expecting to get back 3.

Thanks for your help!

---

Notes

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

I am pretty sure I do not need to use rowid

Answer 1

If you're after the unique identifier of each row in the table you need ROWID, not ROWNUM.

ROWNUM is a pseudocolumn that can change each time a bit of SQL is executed (it's worked out at query time)

Answer 2

Use this query:

select rownum2 from ( select dogid as dogId, rownum rownum2 from ids dog order by dogname asc) where dogid =206;

That will return the rownumber of dogid 206, which is 3.

Answer 3

See if this works for you:

Answer

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

Results

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')

Answer 4

I suspect what you want is to use an analytic function (RANK, DENSE_RANK, or ROW_NUMBER), i.e.

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

If the ID column in the CANINES table were not unique, RANK, DENSE_RANK, and ROW_NUMBER) would treat ties differently.

If you want to do this solely with 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 5

your query is :: select rounum from cannies dog where dog_id=206;

the ans is 1 becoz... it first create the virtual table with the condition in where clause... we have only one record in the table with the matching of where clause.. so,one record is coming..then rownum is assigned for the rows in the virtual table.. for the qualifying record rownum is 1...so,ans is 1..not 3.