SQL-Select last two rows in query

[Sharma]

My where clause qualifies 10 rows.
But I need only last two rows entered in table to be displayed.

FYI, I have a date column in the table which I can use to orderby
Database - Oracle 8.0.5
OS - NT Server

[inosov]

connect scott/tiger

select ename,hiredate from
(select ename, hiredate from emp order by hiredate desc)
where rownum < 3;

It works correctly on 8.1.7,
but I'm not sure whether it works on 8.0.5.
Please try.

[kmesser]

inosov,

That works fine as long as you don't care which two rows get returned from those 10 with the most recent hiredate. Your solution will work, but only if Sharma fixes the data to be more specific in terms of the date and time of the insert.

[inosov]

select ename, hiredate from
(select distinct -(hiredate - TO_DATE('01-JAN-1900','DD-MON-YYYY')) s,
ename, hiredate from emp)
where rownum < 3;

If the previous solution doesn't work on 8.0.5, try this one.

Both examples show how to get two records with the most recent hiredates.
Of course, in your query you will use the column storing date of insert, not hiredate.


[Edited by inosov on 04-10-2001 at 07:24 PM]

[kmesser]

inosov,

What's the difference?

Sharma wants THE LAST TWO RECORDS INSERTED INTO THE DATABASE! Not just any two of all the records stored on the most recent date. If there are three records stored in the database on that same day, which two will you get? You don't know unless the time is stored also. A timestamp should be used here, not just a date. Then your solution is perfect.

[akkerend]

You need or a column with a timestamp, date column filled with sysdate as record is inserted:

SELECT e1.*
FROM emp e1
WHERE 2 > (
SELECT COUNT(*)
FROM emp e2
WHERE e2.timestamp > e1.timestamp);

Or a column based on a sequence, e.g. empno:

SELECT e1.*
FROM emp e1
WHERE 2 > (
SELECT COUNT(*)
FROM emp e2
WHERE e2.empno > e1.empno);

[chrisrlong]

Just because I can't help myself...

What everyone is saying is true - if you want the absolute last two records inserted, you need some field that tells you when, precisely, they were inserted.

However...

I would caution against simply calling this field 'timestamp'. The name timestamp is customarily used for a column that is used for optimistic concurrency, and should really be considered 'reserved' for that purpose. If most people were to see a field named timestamp, they would assume that it was used for optimistic concurrency. *In which case*, it would indeed be filled upon insert *but also* modified upon update. This would not, then, be the functionality you would desire.

Therefore, if you need to set *and keep* a column with the precise time the record was inserted, I would certainly suggest a trigger-populated DATE column, but I might suggest calling it something like 'Inserted_TS' instead, to better identify its use.

Just my .02,

- Chris

[chrisrlong]

Some more thoughts...

Performance!!! (Imagine that, I'm pushing performance )

Now, I didn't have a timestamped column handy so I used the PK. I'm most interested in using a column that's indexed, as I assume the Inserted_TS would be. Here are some results on an ~11,000 record table:

SELECT /* test8 */
e1.org_pk, e1.legal_st_cd
FROM org e1
WHERE 2 > (
SELECT COUNT(*)
FROM org e2
WHERE e2.org_pk > e1.org_pk);

SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=1137 Bytes=15918)
---FILTER
------TABLE ACCESS (FULL) OF ORG (Cost=33 Card=1137 Bytes=15918)
------SORT (AGGREGATE)
---------INDEX (RANGE SCAN) OF XPKORG (UNIQUE) (Cost=2 Card=1137 Bytes=4548)

LR: 312725
Time: 333 secs
----------------------------------------------------------------
SELECT /* test8 */
org_pk, legal_st_cd from
(select org_pk, legal_st_cd from org order by org_pk desc)
where rownum < 3

SELECT STATEMENT Optimizer=CHOOSE (Cost=73 Card=22734 Bytes=522882)
---COUNT (STOPKEY)
------VIEW (Cost=73 Card=22734 Bytes=522882)
---------SORT (ORDER BY STOPKEY) (Cost=73 Card=22734 Bytes=318276)
------------TABLE ACCESS (FULL) OF ORG (Cost=33 Card=22734 Bytes=318276)

LR: 845
Time : 0.69 secs
----------------------------------------------------------------
SELECT /* test8 */
* from
(select * from org order by org_pk desc)
where rownum < 3

SELECT STATEMENT Optimizer=CHOOSE (Cost=350 Card=22734 Bytes=87321294)
---COUNT (STOPKEY)
------VIEW (Cost=350 Card=22734 Bytes=87321294)
---------TABLE ACCESS (BY INDEX ROWID) OF ORG (Cost=350 Card=22734 Bytes=11162394)
------------INDEX (FULL SCAN DESCENDING) OF XPKORG (UNIQUE) (Cost=25 Card=22734)

LR: 4
Time: 0.05 secs
----------------------------------------------------------------
SELECT /* test8 */
org_pk, legal_st_cd from
(select distinct
-(org_pk - 100000) s,
org_pk, legal_st_cd---from org)
where rownum < 3;

SELECT STATEMENT Optimizer=CHOOSE (Cost=73 Card=22734 Bytes=522882)
---COUNT (STOPKEY)
------VIEW (Cost=73 Card=22734 Bytes=522882)
---------SORT (UNIQUE STOPKEY) (Cost=73 Card=22734 Bytes=318276)
------------TABLE ACCESS (FULL) OF ORG (Cost=33 Card=22734 Bytes=318276)

LR: 845
Time: 0.17
----------------------------------------------------------------

These were done, as always, multiple times on an isolated server.

What to note:
- If you can get the optimizer to do an INDEX SCAN DESCENDING, this is your best bet. Note how simply switching to * from a column list did this (wierd, huh?) . Of course, one would *never* use SELECT * in a production-query. Hinting it would be a better idea.
- DO NOT use the > COUNT sub-select proposed by akkerend. Sorry akkerend, but as the results show, that one was pretty bad . Not that it's your fault, I've seen this solution a lot of places too, including from Joe Celko. It just goes to show that nobody is perfect

Again, just trying to raise the awareness of how drastically one's choice of solutions can affect performance.

- Chris

[Sharma]

I used the second query/posting sent by "inosov" and it work fine for me. I am using timestamp also along with the date.

Thnakyou Guys for all you support.

[Krutsinger]

Couldn't she have just used ROWID to find the last records?