Skip to Main Content
  • Questions

  • Returning matching or highest row in a SELECT statement

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mr.

Asked: September 17, 2016 - 10:18 pm UTC

Last updated: September 20, 2016 - 5:03 pm UTC

Version: 12 C

Viewed 1000+ times

You Asked

I'm trying to retrieve only two rows when joining these two table.

In table A: I have an ID and Date

In table B: I have an ID and Timestamp.

My select is retrieving 4 rows, however, I want to pick the timestamp from table B that matches the Date from table A (Condition 1). When two Dates are found (Condition 2), I want to pick the one with the most recent timestamp.

Here's the model:

SELECT A.ID, B.TIMESTAMP FROM A, B
WHERE A.ID = B.ID
Table A

ID || Date
1 || 09/17/2016
2 || 02/01/2016
TABLE B

ID||TIMESTAMP
1 || 17-SEP-16 12.50.40.754543000 PM
1 || 19-SEP-16 12.56.40.754543000 PM
2 || 02-FEB-08 10.31.36.154000000 PM
2 || 02-FEB-08 10.31.32.284000000 PM
Results

ID || TIMESTAMP
1 || 17-SEP-16 12.50.40.754543000 PM
2 || 02-FEB-08 10.31.36.154000000 PM

and Chris said...

Sounds like you want to:

- Join where the timestamp is between the date and the date + 1
- Group by id
- Return the max timestamp

e.g.

create table a (
  id int, dt date
);
create table b (
  id int, ts timestamp
);

insert into a values (1, to_date('09/17/2016', 'mm/dd/yyyy'));
insert into a values (2, to_date('02/02/2008', 'mm/dd/yyyy'));

insert into b values (1, to_timestamp('17-SEP-16 12.50.40.754543000 PM', 'dd-MON-yy hh.mi.ss.ff9 AM'));
insert into b values (1, to_timestamp('19-SEP-16 12.56.40.754543000 PM', 'dd-MON-yy hh.mi.ss.ff9 AM'));
insert into b values (2, to_timestamp('02-FEB-08 10.31.36.154000000 PM', 'dd-MON-yy hh.mi.ss.ff9 AM'));
insert into b values (2, to_timestamp('02-FEB-08 10.31.32.284000000 PM', 'dd-MON-yy hh.mi.ss.ff9 AM'));

select a.id, max(b.ts)
from   a
join   b
on     a.id = b.id
and    a.dt <= b.ts
and    a.dt + 1 > b.ts
group  by a.id;

ID  MAX(B.TS)             
1   17-SEP-2016 12.50.40  
2   02-FEB-2008 22.31.36

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Inequality

Stew Ashton, September 20, 2016 - 8:32 am UTC

Chris, your solution uses an inequality join, which may not scale well with large numbers of rows. In 12c, there is an alternative that scales better - you can guess what that is ;)
select * from (
  select id, ts+0 dt, ts from b
  union all
  select id, dt, null from a
)
match_recognize(
  partition by id order by dt, ts nulls first
  measures a.dt dt, b.ts ts
  pattern(a b*)
  define a as ts is null,
    b as dt <= a.dt+1
);

ID DT                  TS
-- ------------------- -------------------------------
 1 2016-09-17 00:00:00 17-SEP-16 12.50.40.754543000 PM
 2 2008-02-02 00:00:00 02-FEB-08 10.31.36.154000000 PM

Chris Saxon
September 20, 2016 - 4:55 pm UTC

Gotta love MATCH_RECNOGNISE

more columns?

Duke Ganote, September 20, 2016 - 2:17 pm UTC

If there are other columns beside the ID, TS and DT of interest on the tables, then windowing functions are appropriate:

WITH -- Oracle 12c example
a ( id, dt ) AS (                              SELECT
     1, DATE'2016-09-17'   FROM DUAL UNION ALL SELECT
     2, DATE'2008-02-02'   FROM DUAL
),
b ( id, ts ) AS (                                                                                        SELECT
     1, to_timestamp('17-SEP-16 12.50.40.754543000 PM', 'dd-MON-yy hh.mi.ss.ff9 AM') FROM DUAL UNION ALL SELECT
     1, to_timestamp('19-SEP-16 12.56.40.754543000 PM', 'dd-MON-yy hh.mi.ss.ff9 AM') FROM DUAL UNION ALL SELECT
     2, to_timestamp('02-FEB-08 10.31.36.154000000 PM', 'dd-MON-yy hh.mi.ss.ff9 AM') FROM DUAL UNION ALL SELECT
     2, to_timestamp('02-FEB-08 10.31.32.284000000 PM', 'dd-MON-yy hh.mi.ss.ff9 AM') FROM DUAL
),
join_and_rank AS (
select a.id, a.dt, b.ts
     , ROW_NUMBER() OVER ( PARTITION BY a.id
                               ORDER BY b.ts DESC ) r#
  from a
  join b
    on a.id = b.id
   and a.dt <= b.ts
   and a.dt + 1 > b.ts
)
select * from join_and_rank
 where r# = 1;

ID DT                  TS                            r#
-- ------------------- ----------------------------- --
 1 2016-09-17 00:00:00 2016-09-17 12:50:40.754543000  1
 2 2008-02-02 00:00:00 2008-02-02 22:31:36.154000000  1

Chris Saxon
September 20, 2016 - 5:03 pm UTC

nice stuff
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.