CODEWITHSUNDEEP
sqloracle-database
Minh Quang
Minh Quang

Reputation: 21

SQL - How to select row by compare date from 2 table

I have 2 table like that:

Table1:

ID |    COMPANY_NAME  | Rank |  FIRST_REGIST_DATE

  1           A           1          2017-09-01
  2           B           2          2017-09-05

Table 2: 

 ID | COMPANY_NAME    | RANK  |   FIRST_REGIST_DATE
 1           A            3          2017-09-03
 2           C            4          2017-09-04

I need to SELECT company with the data FIRST_REGIST_DATE and RANK In case of company have 2 first regist date, we choose the earlier date and RANK greater (EX: company A above will have first date: 2017-09-01)

The Expect result will like that: Company A - Rank 3 - Date:2017-09-01

Please have me to SELECT in that case

Upvotes: 0

Views: 221

Answers (2)

M O'Connell
M O'Connell

Reputation: 487

This technically answers the question but avoids the elephant in the room (which ID takes preference?). As both tables have ID's that may overlap ({B,C} have an ID of 2) rules need to be defined as to which ID takes preference what will the other table ID's be renamed to.

    Select COMPANY_NAME
           ,MIN(FIRST_REGIST_DATE) as REGIST_DATE
    from (
            SELECT *
            FROM #table1
            UNION ALL
            SELECT *
            FROM #table2 
    ) t3
    Group by COMPANY_NAME

OP dont change your question (by adding RANK) after the question has been answered. For your changes: kindly contributed by @toha 

    Select COMPANY_NAME
          ,MIN(FIRST_REGIST_DATE) as REGIST_DATE 
          ,MAX(RANK ) as RANK 
    from ( SELECT * 
           FROM #table1 
             UNION ALL 
           SELECT * 
           FROM #table2 ) t3 
    Group by COMPANY_NAME

Upvotes: 1

phonetic_man
phonetic_man

Reputation: 1088

If I understand the question correctly you have two tables with data containing company details and first registration date and you want to show the row with the earliest first registration date. The following query will help you.

SELECT company_name, MIN(fisrt_regist_date)
(
SELECT company_name, fisrt_regist_date
FROM table1
UNION ALL
SELECT company_name, fisrt_regist_date
FROM table2
) tab1
FROM tab1
GROUP BY company_name

The above query will combine the results of the first table and the second table and then show you the details of the company along with the oldest registration date.

Upvotes: 1

Related Questions