Select max and show last result

Dev Shed Forums Sponsor:

July 24th, 2012, 09:51 AM

dibblejon

Registered User

Join Date: Jun 2012

Posts: 17

Time spent in forums: 3 h 48 m 31 sec
Reputation 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

July 24th, 2012, 10:53 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 56 m 37 sec
Reputation Power: 284

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

July 25th, 2012, 02:39 AM

dibblejon

Registered User

Join Date: Jun 2012

Posts: 17

Time spent in forums: 3 h 48 m 31 sec
Reputation 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,

Select max and show last result

Developer Shed Advertisers and Affiliates