DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: SQL-Select last two rows in query

  1. #1
    Join Date
    Nov 2000
    Posts
    224
    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

  2. #2
    Join Date
    Apr 2001
    Posts
    37
    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.
    inosov
    Brainbench MVP for Oracle DBA

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    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.

  4. #4
    Join Date
    Apr 2001
    Posts
    37
    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]
    inosov
    Brainbench MVP for Oracle DBA

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    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.

  6. #6
    Join Date
    Jul 2000
    Posts
    296
    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);

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  9. #9
    Join Date
    Nov 2000
    Posts
    224
    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.

  10. #10
    Join Date
    Mar 2001
    Posts
    63
    Couldn't she have just used ROWID to find the last records?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width