CODEWITHSUNDEEP
sqloracle-databasegroup-byoracle-sqldeveloperminmax
Magic due to Logic
Magic due to Logic

Reputation: 121

How to get min and max from 2 tables in SQL

I am Trying to get start date from min ID (ID=1) and end date from max ID (ID=3) but i am not sure how i can retrieve. Following is my data -

enter image description here

Table1 and Table2 are source table. I am trying to get output like 3rd table.

My requirement is get start date from first record of ID and End Date from last record of ID, we can recognize first and and last record with the help of ID field. If ID is min means first record and ID is max then last record

Please help me!

Upvotes: 0

Views: 104

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

Here's one option; presuming you use Oracle (regarding you use Oracle SQL Developer), the x inline view selects

  • start_date which belongs to name with the lowest ID column value for that name (i.e. first_value partition by name order by id)
  • end_date which belongs to name with the highest ID column value for that name (i.e. first_value partition by name order by id DESC)
SQL> with
  2  -- sample data
  3  t1 (pid, name) as
  4    (select 123, 'xyz' from dual union all
  5     select 234, 'pqr' from dual
  6    ),
  7  t2 (id, name, start_date, end_date) as
  8    (select 1, 'xyz', date '2020-01-01', date '2020-07-20' from dual union all
  9     select 2, 'xyz', date '2020-02-01', date '2020-05-30' from dual union all
 10     select 3, 'xyz', date '2020-06-30', date '2020-07-30' from dual union all
 11     --
 12     select 1, 'pqr', date '2020-04-30', date '2020-09-30' from dual union all
 13     select 2, 'pqr', date '2020-05-30', date '2020-09-30' from dual union all
 14     select 3, 'pqr', date '2020-06-30', date '2020-07-01' from dual
 15    )
 16  select a.pid,
 17         x.name,
 18         max(x.start_date) start_date,
 19         max(x.end_date)   end_date
 20  from t1 a join
 21      (
 22        -- start_date: always for the lowest  T2.ID value row
 23        -- end_date  : always for the highest T2.ID value row
 24        select b.name,
 25               first_value(b.start_date) over (partition by b.name order by b.id     ) start_date,
 26               first_value(b.end_date)   over (partition by b.name order by b.id desc) end_date
 27        from t2 b
 28       ) x
 29       on a.name = x.name
 30  group by a.pid,
 31           x.name
 32  order by a.pid;

       PID NAME START_DATE END_DATE
---------- ---- ---------- ----------
       123 xyz  01/01/2020 07/30/2020
       234 pqr  04/30/2020 07/01/2020

SQL>

Upvotes: 1

Related Questions