Home » SQL & PL/SQL » SQL & PL/SQL » Select last record in a group depending on a condition (10g)
Select last record in a group depending on a condition [message #381067] Thu, 15 January 2009 02:38 Go to next message
jallen@haynes.co.uk
Messages: 5
Registered: January 2009
Location: Somerset
Junior Member
Hi,

I would like to be able to select the last record in a group based upon a condition. So in the sample data below I want to select the records with **** next to them i.e. if the last record in the group (group by order_number) has a trans_type = 'Declined' I want to select the record otherwise I want to ignore it.
Order Number	Trans Type	Trans Date
0001		Declined	01/01/2009 10:10
0001		Approved	01/01/2009 10:20
0002		Declined	01/01/2009 10:21
0002		Declined	01/01/2009 10:22
0002		Declined	01/01/2009 10:23 ****
0003		Declined	01/01/2009 10:30
0003		Approved	01/01/2009 10:34
0004		Declined	01/01/2009 10:40 ****
0005		Declined	01/01/2009 10:42
0005		Declined	01/01/2009 10:43
0005		Declined	01/01/2009 10:44
0005		Declined	01/01/2009 10:45 ****	

Any help on the sql statement I need to achieve this would be much appreciated.

Many thanks,

J.

[EDITED by LF: applied [pre] tags]

[Updated on: Thu, 15 January 2009 03:10] by Moderator

Report message to a moderator

Re: Select last record in a group depending on a condition [message #381071 is a reply to message #381067] Thu, 15 January 2009 02:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to look at analytic functions, particularly (depending on how you choose to do it) FIRST_VALUE/LAST_VALUE or LAG/LEAD

FIRST/LAST VALUE will let you get the solution in a single query, but you'll need to understand the details of the WIINDOWING clause

Have a try, and come back to us if you've got a problem.

FIRST_VALUE
LEAD
Analytic Fiunctions
Re: Select last record in a group depending on a condition [message #381072 is a reply to message #381067] Thu, 15 January 2009 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use MAX in a subquery or have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

Regards
Michel
Re: Select last record in a group depending on a condition [message #381074 is a reply to message #381072] Thu, 15 January 2009 02:58 Go to previous messageGo to next message
jallen@haynes.co.uk
Messages: 5
Registered: January 2009
Location: Somerset
Junior Member
Michael,

Thanks for that, but my sql is fairly poor. If you have a spare 5 minutes, I would be very grateful if you could let me have some sample sql.

Thanks in advance.
Re: Select last record in a group depending on a condition [message #381083 is a reply to message #381074] Thu, 15 January 2009 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Select last record in a group depending on a condition [message #381091 is a reply to message #381083] Thu, 15 January 2009 03:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My test case:
WITH your_table AS
(
SELECT '0001' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:10', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0001' ord_no, 'Approved' trans_tp, to_date('01/01/2009 10:20', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0002' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:21', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0002' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:22', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0002' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:23', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0003' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:30', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0003' ord_no, 'Approved' trans_tp, to_date('01/01/2009 10:34', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0004' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:40', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0005' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:42', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0005' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:43', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0005' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:44', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual UNION ALL
SELECT '0005' ord_no, 'Declined' trans_tp, to_date('01/01/2009 10:45', 'dd/mm/yyyy hh24:mi') trans_dt FROM dual
)
SELECT ord_no
     , trans_tp
     , trans_dt
FROM (  SELECT ord_no
             , trans_tp
             , trans_dt
             , RANK() OVER ( PARTITION BY ord_no ORDER BY trans_dt DESC) rnk
        FROM your_table
        ORDER BY ord_no
     )
WHERE rnk = 1
AND   trans_tp = 'Declined'
/


The WITH part is ony there to create test data on the fly, you just need to focus on the SELECT.

The inner select will use an analytic function like suggested by JRowbottom and Michel Cadot. I'm not going to explain it in detail but basically the RANK() will return a rank (surprising, isn't it?) for each group of ord_no records, sorted by the transaction date. But you want to restrict it. This cannot be done in the same select. To get around this restriction, you write a query around this select. The outer select is the result. It is pretty simple: you want only the records with rank 1 (first based on the descending date) and that are 'Declined'.

MHE
Re: Select last record in a group depending on a condition [message #381092 is a reply to message #381091] Thu, 15 January 2009 03:48 Go to previous messageGo to next message
jallen@haynes.co.uk
Messages: 5
Registered: January 2009
Location: Somerset
Junior Member
Many thanks for that. I will give it a try and let you know.
Re: Select last record in a group depending on a condition [message #381093 is a reply to message #381092] Thu, 15 January 2009 03:50 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps a little bit simpler way would be
SQL> select * from test
  2  where trans_date in (select max(trans_date)
  3                       from test
  4                       group by order_no
  5                      )
  6    and trans_type = 'Declined';

ORDE TRANS_TYPE           TRANS_DATE
---- -------------------- -------------------
0002 Declined             01.01.2009 10:23:00
0004 Declined             01.01.2009 10:40:00
0005 Declined             01.01.2009 10:45:00

SQL>
Re: Select last record in a group depending on a condition [message #381095 is a reply to message #381093] Thu, 15 January 2009 03:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
That's one hell of an archaic query! And it is missing something: you might want to include ord_no in the 'IN' clause.

Edit: I suspect it might be faster than an analytic function though...

MHE

[Updated on: Thu, 15 January 2009 03:54]

Report message to a moderator

Re: Select last record in a group depending on a condition [message #381096 is a reply to message #381095] Thu, 15 January 2009 04:01 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes (archaic ./fa/1601/0/), yes (might include ord_no ./fa/3314/0/), yes (suspicion ./fa/1600/0/).

Well, regarding the sample input and output, it does the job. So, I'll leave it "as is"; the OP might use a more modern approach or modify it in any other way. ./fa/1986/0/
Previous Topic: What may be problem?
Next Topic: Replicating Packages across instances
Goto Forum:
  


Current Time: Wed Dec 07 10:30:01 CST 2016

Total time taken to generate the page: 0.13248 seconds