Getting most uptodate record from DB2

Posted on 2012-09-15
Last Modified: 2012-09-18
I tried a SQL of the following order but not sure if this is doing the correct way.

select *
from T
left join (select ID, max(A) from T) a
on T.ID=a.ID and T.A=a.A
left join (select ID, max(B) from T) B
on T.ID=b.ID and T.B=b.B
where T.X=1 and Y=2 and Z=3.
Question by:LuckyLucks
    1 Comment
    LVL 37

    Accepted Solution

    it's not clear how you decide which record is the most updated one but assuming you can get it by doing an order by, for example order by a,b
    then you need this

    select *
    from (select *, row_number() over(order by a desc, b desc) as rown from t) t
    where rown=1

    this way, the order by dictates the order of the records and by choosing rown=1 you choose the most updated one

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Title # Comments Views Activity
    Change variables in SQL table 6 54
    Invalid Identifier Error 3 48
    query Syntax 4 21
    Php Sql Query 7 19
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now