Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    46

    Usage of max(rowid) in sql

    Hello,

    This command,

    SELECT ROWID,creatd_dt FROM <tablename>
    where rec_id=1234;

    gives this output.

    ROWID CREATD_DT
    ------------------ ---------
    AAAMehAADAAA2c6AAG 19-MAY-06
    AAAMehAADAAA2c6AAI 22-MAY-06
    AAAMehAADAAA2c/ACF 19-MAY-06

    When I am trying to take the maximum rowid of it,

    1 select max(r)
    2 from
    3 (
    4 SELECT ROWID r,creatd_dt FROM <tablename>
    5* where rec_id=1234);

    MAX(R)
    ------------------
    AAAMehAADAAA2c/ACF

    I am getting this as the result whereas I should get

    AAAMehAADAAA2c6AAI as that was the latest I inserted into the table.

    Kindly advice me a method of fetching the correct rowid.

    Thanks

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,133
    Why do you want the max rowid?

    Alan

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by sathidevi
    I am getting this as the result whereas I should get AAAMehAADAAA2c6AAI as that was the latest I inserted into the table.
    ROWIDs are not sorted in the insert order, they are just guaranteed to be unique, not sorted in any manner. The only way to keep track of the insert order is to add an INSERT_DATE field populated with SYSDATE or so at insert time.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,000
    Code:
    select ROWID 
         , creatd_dt
      from daTable as T
     where rec_id = 1234
       and creatd_dt
         = ( select max(creatd_dt)
               from daTable
              where rec_id = T.rec_id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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