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  |
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 #381091 is a reply to message #381083] |
Thu, 15 January 2009 03:46  |
 |
Maaher
Messages: 7065
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
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 23:44:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
