-
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
-
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
-
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);
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|