#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0

    Select max and show last result


    Hi

    I have a table that has info like:

    account | date | reason

    abc001 2012-07-01 00:00:00 1
    abc001 2012-07-03 14:00:26 3
    abc001 2012-07-06 14:00:00 2
    sac870 2012-07-01 00:00:00 1
    sac870 2012-07-23 00:00:00 2

    I want to get resuts by using a max on the date and returning the reason associated with that group - so for the exanple above I want to see

    abc001 2012-07-06 14:00:00 2
    sac870 2012-07-23 00:00:00 2

    Any help would be great
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Code:
    select account, date, reason
    from (
       select account, 
              date,
              reason,
              row_number() over (partition by account order by date desc) as rn
       from info_table
    ) t
    where rn = 1
    order by account
    or as an alternative:
    Code:
    select account, date, reason
    from info_table t1
    join (
        select account, max(date) as max_date
        from info_table
        group by account) tm on tm.account = t.account and tm.max_date = t1.date
    order by account;
    although I think the first version will be faster.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    17
    Rep Power
    0
    Thanks - have added in this code


    select slstop.account, slstop.sys_date, slstop.stop_flag
    from (
    select slstop.account,
    slstop.sys_date,
    slstop.stop_flag,
    row_number() over (partition by slstop.account order by slstop.sys_date desc) as rn
    from slstop
    ) t
    where rn = 1
    order by slstop.account

    But get this error?

    ERROR: missing FROM-clause entry for table "slstop"
    LINE 1: select slstop.account, slstop.sys_date, slstop.stop_flag

    Tried adding this

    select slstop.account, slstop.sys_date, slstop.stop_flag
    from slstop(
    select slstop.account,
    slstop.sys_date,
    slstop.stop_flag,
    row_number() over (partition by slstop.account order by slstop.sys_date desc) as rn
    from slstop
    ) t
    where rn = 1
    order by slstop.account

    But get this error? Where am i going wrong?

    ERROR: syntax error at or near "select"
    LINE 3: select slstop.account,

IMN logo majestic logo threadwatch logo seochat tools logo