Results 1 to 5 of 5

Thread: Last records

  1. #1
    Join Date
    Feb 2004
    Location
    Dordrecht (Netherlands)
    Posts
    2

    Unanswered: Last records

    I am trying to get all the last records of a clients document in a table. The table consists of a docnumber, code (who changed it), a changedate, and a clientID.
    rec1 123 AAA 01-01-2003 123123
    rec2 123 EEE 01-02-2004 123123
    rec3 222 CCC 31-12-2003 123123
    rec4 222 DDD 31-01-2004 123123
    rec5 222 FFF 01-02-2004 123123

    When I do a select, I want to get rec2 and rec5 (rec2 is the last record of document 123 of client 123123 and rec5 is the last record of document 222 of client 123123.

    How do I code the select-statement in Oracle (8)?

    Thanx in advance,
    Paul

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I understand your example records well, "last record" is last regarding "changedate". If so, would this query help?
    Code:
    SELECT m.docnumber, m.code, m.changedate, m.clientid
    FROM mytable m
    WHERE m.changedate = (SELECT MAX(m1.changedate) FROM mytable m1
                          WHERE m1.docnumber = m.docnumber
    					 );

  3. #3
    Join Date
    Feb 2004
    Location
    Dordrecht (Netherlands)
    Posts
    2
    That might do the job. Thanks a lot!

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Any time

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One minor point is that it is a correlated subquery so it will run OK providing you havent got lots client ids. If you do then do the following


    SELECT m.docnumber, m.code, m.changedate, m.clientid
    FROM mytable m,
    (SELECT docnumber, max(m1.changedate) lastchange FROM mytable m1 group by docnumber) x
    WHERE m.changedate = x.lastchange
    and m.docnumber=x.docnumber

    Of course this is assuming changedate is unique (date fields have a resolution of one second). If you have a large number of rows then an index on docnumber, changedate would also help.

    Alan

Posting Permissions

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