OraFAQ Forum: SQL & PL/SQL » Last successful action using one query

Home » SQL & PL/SQL » SQL & PL/SQL » Last successful action using one query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Last successful action using one query [message #3003]

Wed, 28 August 2002 16:58

Aklil
Messages: 1
Registered: August 2002

Junior Member

I have a table:
ID Account# Action Success
1 1 enrol S
2 1 deenrol S
3 2 enrol S
4 2 deenrol F
5 3 enrol S
6 3 denrol F
7 3 denrol S

I want to pick the last successful action for an account number?

I've used distinct but that only allows me to do with one column (I need all the attributes in the select statement), so I have to do multiple queries. If possible I'd like to do it in one go.

So in this case the query should return:
Accts with IDs: 2, 3 and 7.

Any ideas? I think it's something to do with max(id)?

Thanks
Aklil

Report message to a moderator

Re: Last successful action using one query [message #3006 is a reply to message #3003]

Wed, 28 August 2002 22:23

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

select *
  from t
 where id in (select max(id)
                from t
               where action = 'S'
               group by account_no);

Report message to a moderator

Previous Topic:	Help!Query optimization Docs
Next Topic:	Creation of Temp table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 01:18:24 CDT 2024