A tricky sql requirement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: A tricky sql requirement

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    ID NOT NULL NUMBER(38)
    ACCOUNTNUMBER NOT NULL NUMBER(38)
    ORIGINATINGACCOUNT NOT NULL NUMBER(38)
    STARTTIME NOT NULL DATE
    ENDTIME NOT NULL DATE
    USAGETYPE NOT NULL NUMBER(10)
    TRANSACTIONID NOT NULL NUMBER(38)
    DESCRIPTION VARCHAR2(256)
    CURRENCYCODE VARCHAR2(3)
    AMOUNT NUMBER(28,10)

    PK is ID
    Transaction ID is not unique and is generated randomly..

    I want to know the latest generated transactionid and the max(id) for that transaction..
    How would I write the sql..

    For ex..
    ID Transactionid
    1 55
    2 55
    3 99
    4 99
    5 62
    6 62

    If latest generated transaction id is 62 and then 99, I want the id that is max for these transactionid (6 and 4)..
    I hope I am clear..

    Thanks..

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    your question should have peen posted to the "Oracle development" forum, but anyway ... since I do not know how you identify the latest generated transactions, here is a request that will give you the max(ID) for all the transaction_ids, you just have to add some things in the where clause of the transaction ...

    select a.transaction_id, a.id
    from table a
    where a.id =
    (select max b.id from table b
    where a.transaction_id=b.transaction_id)

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