2

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

jedierikb
  • 12,752
  • 22
  • 95
  • 166

4 Answers4

9

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
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
2

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)

cagcowboy
  • 30,012
  • 11
  • 69
  • 93
  • I want to know the sorted row number for a given id in my query, so I guess I want rownum. Thanks. – jedierikb Feb 10 '09 at 21:24
  • 1
    Seriously, stick to the OP. You can answer what you wanted them to ask after you deal with the OP. –  Feb 10 '09 at 21:48
0

In order to accomplish this, it would be best to alter the table and add a sequence. This could get sticky if you intend to delete rows. Where, perhaps a better practice would be to use a status column or and start-end-date motif to decide which rows are active and should be returned.

goofy
  • 17
  • 1
0

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')
beach
  • 8,330
  • 3
  • 29
  • 25
  • Regrettably, I cannot substantively change my original query (as you do in your answer) because it is auto-generated by another process. – jedierikb Feb 10 '09 at 21:28
  • Ah, then this approach will not work. But you should be able to use the "rownum" feature in Oracle to get the same pseudo rownumber. – beach Feb 10 '09 at 21:30
  • You can do the same thing much easier in Oracle. "select dogID, DogName, rownum from #ids order by DogName" returns the same result as the group-by query, but will perform much better. – Edwin Feb 10 '09 at 21:37
  • @Beach, are you a long time SQL Server or MySql guy? I believe they didn't have rownum until recently. Hitting the table twice is a huge performance hit compared with just once. –  Feb 10 '09 at 21:50
  • @Mark True, I primarily use MSSQL. But I also try and use ANSI-standard SQL when possible. "rownum" is not portable. The hit may not be that big depending on how many rows there are and if the table is properly indexed. – beach Feb 10 '09 at 22:04