Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Unanswered: Urgent help required

    Hi Experts

    I want to get the latest record from member_nomination_details table. This table can have more than 1 record for nominator_cid. I suspect this query will bring incorrect results some times. Although the Inline table is using DESC clause , will rownum always fetch the latest record.

    SELECT entitlement_dt
    , nominee_cid
    , nomination_dt
    FROM ( SELECT entitlement_dt
    , nominee_cid
    ,nomination_dt
    FROM member_nomination_details
    WHERE nominator_cid = 112926493
    ORDER BY entitlement_dt desc
    )
    WHERE ROWNUM < 2 ;

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Rownum is a pseudo column that returns a count of the returned rows. If you do a descending sort in your inter select, rownum < 2 (or rownum = 1) will return the latest record, assuming that your date field contains the time element and you don't put into the database more then 1 row for the id in the same second.

    On a side note, please make sure that your subject line is more meaningful next time. "Urgent Help" can mean anything and many members delibertly skip over messages that don't have meaningful subject lines.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    you could also look for the MAX rowid
    that should get you the most recently inserted record.

    of course if you use a sequence then all your problems are solved.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by The_Duck
    you could also look for the MAX rowid
    that should get you the most recently inserted record.
    No, that wouldn't be a good idea. ROWIDs are a physical address, not an increasing sequence. Sometimes Oracle will fit a new row into an available gap that is not at the physical "end" of the table.

    It's not clear to me how a sequence would help either.

    As Bill has said, the OP's code is fine.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    the sequence is incrementing isn't it?
    In what situation would you have a higher sequence number but also not the
    most recently inserted row?

    I am assuming a decent developer in this situation.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The sequence has no relevance, say an old record had been deleted Oracle could choose to put the latest record in that space. Thus it would have the same rowid as the deleted record had.

    Alan

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by The_Duck
    the sequence is incrementing isn't it?
    In what situation would you have a higher sequence number but also not the
    most recently inserted row?

    I am assuming a decent developer in this situation.
    I see where your confusion comes from: the first sentence of the original post says "I want to get the latest record from member_nomination_details table." However, if you read further you see that what was really meant was "I want to get the latest record from member_nomination_details table for a particular nominator_cid, which isn't quite the same thing!

  8. #8
    Join Date
    Nov 2003
    Location
    India
    Posts
    114

    Thnks Andrewst

    Thanks Andrewst.You are perfect in correcting my statement. That was what I meant. In the future posts I will take care to include all the minute details.

    I was really confused with the sequence stuff. So finally I can say that this sort of query will fetch the latest record for a particular nominator_cid.

    Thanks all who have helped me in this.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by andrewst
    I see where your confusion comes from: the first sentence of the original post says "I want to get the latest record from member_nomination_details table." However, if you read further you see that what was really meant was "I want to get the latest record from member_nomination_details table for a particular nominator_cid, which isn't quite the same thing!
    Yeah, i didn't see that part of the requirement.
    Sorry for the confusion.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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