SQL Join only the last historical row

Question

I'm sitting at the following problem: I'm writing a view where I join several tables to a person table. And I now trying to join the partners table but I only need the historical last valid partner row:

partners table:

id,
name,
married_at,
divorced_at,
died_at,
someone_id

As you can see it's about partners you are/were married with. Someone can have only one partner at a time, but several partners in history. So the last partner of someone (someone_id) may be:

alive and still married
alive but divorced
dead "but still married" (so someone is the widower)

I need to find ONLY the last partner row for someone.

What I got so far:

select *
from someone_table s
left join partners p on (p.someone_id = s.id and (p.divorced_at is null and p.died_at is null) )

But this - obvious as it is - gives me only partners who are still alive and still married. Sure these partners are the last partners of someone but all other "someones" whos last partner is divorced or dead won't be in the result of the statement. How do I get the other ones and only one row for each someone?

I also tried a select-statement as table and using of rownum

select *
from someone s,
(select * from partners p where p.someone_id = s.id and ROWNUM = 1 order by p.married_at)

But this statement always fails with an "invalied identifier s.id" error

Note: The table structure is fixed and can't be changed. DBMS is oracle.

Thanks in advance

edit: sample data

partners_table

╔════╦═════════╦════════════╦═════════════╦════════════╦════════════╗
║ id ║  name   ║ married_at ║ divorced_at ║  died_at   ║ someone_id ║
╠════╬═════════╬════════════╬═════════════╬════════════╬════════════╣
║  1 ║ partner ║ 01.01.2000 ║             ║            ║         12 ║
║  2 ║ honey1  ║ 15.01.2000 ║ 15.01.2001  ║            ║         15 ║
║  3 ║ honey2  ║ 16.02.2001 ║             ║            ║         15 ║
║  4 ║ beauty  ║ 23.03.2005 ║             ║ 25.03.2005 ║         16 ║
║  5 ║ lady1   ║ 11.11.2000 ║ 11.12.2000  ║            ║         20 ║
║  6 ║ lady2   ║ 12.12.2000 ║ 01.01.2001  ║            ║         20 ║
║  7 ║ lady3   ║ 02.02.2001 ║             ║ 04.02.2004 ║         20 ║
║  8 ║ lady4   ║ 05.05.2005 ║             ║            ║         20 ║
║  9 ║ mate    ║ 23.06.2003 ║ 12.12.2009  ║            ║         25 ║
╚════╩═════════╩════════════╩═════════════╩════════════╩════════════╝

last historical rows would be:

╔════╦═════════╦════════════╦═════════════╦════════════╦════════════╗
║ id ║  name   ║ married_at ║ divorced_at ║  died_at   ║ someone_id ║
╠════╬═════════╬════════════╬═════════════╬════════════╬════════════╣
║  1 ║ partner ║ 01.01.2000 ║             ║            ║         12 ║
║  3 ║ honey2  ║ 16.02.2001 ║             ║            ║         15 ║
║  4 ║ beauty  ║ 23.03.2005 ║             ║ 25.03.2005 ║         16 ║
║  8 ║ lady4   ║ 05.05.2005 ║             ║            ║         20 ║
║  9 ║ mate    ║ 23.06.2003 ║ 12.12.2009  ║            ║         25 ║
╚════╩═════════╩════════════╩═════════════╩════════════╩════════════╝

Can you update your question with some sample data along with the expected output, please? Also, couldn't the last partner of someone be dead and divorced? — Boneist, May 12 '15 at 10:32
I added some sample-data. Please comment if there are more questions — bish, May 12 '15 at 10:49

score 3 · Accepted Answer · answered May 12 '15 at 11:12

This ought to do what you want:

with partners (id, name, married_at, divorced_at, died_at, someone_id) as (select 1, 'partner', to_date('01/01/2000', 'dd/mm/yyyy'), null, null, 12 from dual union all
                                                                           select 2, 'honey1', to_date('15/01/2000', 'dd/mm/yyyy'), to_date('15/01/2001', 'dd/mm/yyyy'), null, 15 from dual union all
                                                                           select 3, 'honey2', to_date('16/02/2001', 'dd/mm/yyyy'), null, null, 15 from dual union all
                                                                           select 4, 'beauty', to_date('23/03/2005', 'dd/mm/yyyy'), null, to_date('25/03/2005', 'dd/mm/yyyy'), 16 from dual union all
                                                                           select 5, 'lady1', to_date('11/11/2000', 'dd/mm/yyyy'), to_date('11/12/2000', 'dd/mm/yyyy'), null, 20 from dual union all
                                                                           select 6, 'lady2', to_date('12/12/2000', 'dd/mm/yyyy'), to_date('01/01/2001', 'dd/mm/yyyy'), null, 20 from dual union all
                                                                           select 7, 'lady3', to_date('02/02/2001', 'dd/mm/yyyy'), null, to_date('04/02/2004', 'dd/mm/yyyy'), 20 from dual union all
                                                                           select 8, 'lady4', to_date('05/05/2005', 'dd/mm/yyyy'), null, null, 20 from dual union all
                                                                           select 9, 'mate', to_date('23/06/2003', 'dd/mm/yyyy'), to_date('12/12/2009', 'dd/mm/yyyy'), null, 25 from dual)
select id,
       name,
       married_at,
       divorced_at,
       died_at,
       someone_id
from   (select id,
               name,
               married_at,
               divorced_at,
               died_at,
               someone_id,
               row_number() over (partition by someone_id order by married_at desc) rn
        from   partners)
where  rn = 1;

        ID NAME    MARRIED_AT DIVORCED_AT DIED_AT    SOMEONE_ID
---------- ------- ---------- ----------- ---------- ----------
         1 partner 01/01/2000                                12
         3 honey2  16/02/2001                                15
         4 beauty  23/03/2005             25/03/2005         16
         8 lady4   05/05/2005                                20
         9 mate    23/06/2003 12/12/2009                     25

My +1 is for noticing that the `divorced_at` and `died_at` are irrelevant to the question. I should have thought about it myself :-) — Zohar Peled, May 12 '15 at 11:27
It was Tobsey who noticed it first, although it's a shame that their answer doesn't answer the question. I, er, "borrowed" the idea and ran with it! — Boneist, May 12 '15 at 11:35
Never heard of this `over (partition by...)` but it works and I can easly use it a join. Thank you! — bish, May 12 '15 at 12:14
If you've never come across analytic functions before, they're something that you would do well to research. At their most basic, they allow you to aggregate data across rows (like you would with group by), but without collapsing the rows down. Very handy for running totals, finding the first row etc etc — Boneist, May 12 '15 at 13:34

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

If I understand your question (and I believe I do), you should try something like this:

SELECT *
FROM someone_table s
left join (
    SELECT *
    FROM (
        SELECT *
        FROM partners p
        WHERE p.someone_id = s.id
        ORDER BY GREATEST(died_at, divorced_at, married_at)
    ) x
    WHERE ROWNUM = 1 
 ) y

Note: I'm not an oracle guy, most of my work is with sql server, but according to this post greatest should work on oracle database.

edited May 23 '17 at 12:06

Community

1
1

answered May 12 '15 at 10:43

Zohar Peled

79,642
10
69
121

`TOP 1`is not valid Oracle syntax – Tobsey May 12 '15 at 10:43
the problem with your usage of `GREATEST` here is that if it encounters a null value, it returns null. – Boneist May 12 '15 at 11:02
yeah, well, I guess I should try harder if I want to answer oracle questions... :-) I would probably be easy for me to solve using sql server. – Zohar Peled May 12 '15 at 11:19
I have the same thing with SQL Server, so no worries! *{:-) – Boneist May 12 '15 at 11:34

Rajesh · Answer 3 · 2015-05-12T12:29:36.503

1

Approach 1 :

SELECT 
    * 
FROM 
    partners 
WHERE 
    someone_id = $someone_id 
AND 
    married_at = (SELECT MAX(married_at) FROM partners WHERE someone_id = $someone_id GROUP BY someone_id);

Approach 2 :

SELECT 
    p.*
FROM
    partners p
INNER JOIN
(
    SELECT 
        someone_id, MAX(married_at) as lastmarried_at
    FROM 
        partners
    GROUP BY
        someone_id
) m
ON m.someone_id = p.someone_id AND m.lastmarried_at = p.married_at
where p.someone_id in ($someone_id1, $someone_id2);

Note: Replace $someone_id with actual value

edited May 12 '15 at 12:29

answered May 12 '15 at 11:40

Rajesh

2,135
1
12
14

Hi Rajesh, this gives the expected result when just selecting the partners table. But when I use it in my join i also got the same error message as describe in my question, see 2nd select statement there. :/ – bish May 12 '15 at 12:04
Hi Bish, see approach 2; both query give same result. If you still can't use those in your view/join...may be you can open new thread to correct your view/join; marking this Answer accepted. – Rajesh May 12 '15 at 12:16

Tobsey · Answer 4 · 2015-05-12T13:04:11.193

0

SELECT
    ID,
    Name,
    MAX(Married_At) LastMarriedAt,
    MAX(Divorced_At) KEEP (DENSE_RANK LAST ORDER BY Married_At) LastDivorcedAt,
    MAX(Died_At) KEEP (DENSE_RANK LAST ORDER BY Married_At) LastDiedAt,
    MAX(Someone_ID) KEEP (DENSE_RANK LAST ORDER BY Married_At) LastSomeoneID
FROM
    Partners
GROUP BY
    ID,
    Name

See example: http://sqlfiddle.com/#!4/3c073/1

EDIT: Based on sample data, some columns need to be moved around

SELECT
    Someone_ID,
    MAX(Name) KEEP (DENSE_RANK LAST ORDER BY Married_At) Name,
    MAX(Married_At) LastMarriedAt,
    MAX(Divorced_At) KEEP (DENSE_RANK LAST ORDER BY Married_At) LastDivorcedAt,
    MAX(Died_At) KEEP (DENSE_RANK LAST ORDER BY Married_At) LastDiedAt,
    MAX(ID) KEEP (DENSE_RANK LAST ORDER BY Married_At) LastID
FROM
    Partners
GROUP BY
    Someone_ID

edited May 12 '15 at 13:04

answered May 12 '15 at 10:43

Tobsey

3,390
14
24

But even without seeing your fiddle. I don't think this is the result I need. Please have a look at my sample data which should make my question clearer. – bish May 12 '15 at 10:55
Doesn't give expected results. Also data incorrectly setup. – Rajesh May 12 '15 at 11:15
@Rajesh Question wasn't clear that the someone_id was the key when I gave my answer. To me the question as originally formed was that ID referred to the user in question and the someone_id referred to their partner. I've added an updated query – Tobsey May 12 '15 at 13:03

SQL Join only the last historical row

4 Answers4