Question on Rowid

[Fiona]

I have this table with n number of rows. I need to be able to calculate the difference between the max row and the one previous to that.

To get the max rowid, I use the following syntax:
select * from tablename
where rowid=(select max(rowid) from tablename)

But I don't know how to select the row that is immediately previous to the max(rowid): i.e. max(rowid) - 1.
(not the minimum rowid)

Anyone knows the syntax for that, please let me know!

Thanx in advance
Fiona

[m1l]

Fiona,

This is pretty nasty, but does what you ask

select * from tablename
where rowid=(select max(rowid) from tablename
where rowid != (select max(rowid) from tablename))

Regards,

Mike

[Highlander]

Try this, using the EMP table

SELECT MAX(A.hiredate) first,MAX(B.hiredate) second
FROM emp A, emp B
WHERE B.hiredate < (SELECT MAX(hiredate) FROM emp);

[chrisrlong]

This will get you the second one. If you want the both the first and second, change it to <=2. Also, since you did not list all the fields in the SELECT, I was unable to remove the QUERY_ROWNUM field from the final resultset:

SELECT
*
FROM
(
SELECT
T.*,
ROWNUM
AS QUERY_ROWNUM
FROM
TABLENAME T
ORDER BY
T.ROWID DESC
)
WHERE
QUERY_ROWNUM = 2

Hope this helps,

- Chris