CODEWITHSUNDEEP
sqloracle-databasejoinnested
Jarod 81
Jarod 81

Reputation: 21

SQL Oracle - max function

I need some help with this query, i need to get code name address with the most recent date per code (date1 is < date6 in this example)

Tab1

   code address
====================
    cod1    addr1
    cod2    addr2
    cod3    addr3
    cod4    addr4

Tab2

    code    date     name
===========================
    cod1    date1    name1
    cod1    date2    name1
    cod1    date3    name2
    cod2    date3    name3
    cod3    date5    name4
    cod3    date6    name5
    cod4    date3    name6

So i write

SELECT Tab1.code, Tab1.address, Tab2.name, max(Tab2.date)
FROM Tab1, Tab2
WHERE Tab1.code=Tab2.code
GROUP BY
Tab1.code, Tab1.address, Tab2.name
ORDER BY
Tab2.name

but I do not get what I want, the same code multiple times with different dates, so I guess a join or a nested select is necessary, but being a little ignorant I'm asking here for help

Upvotes: 1

Views: 2469

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

You can use either analytics or aggregate functions.

Analytics:

SELECT code, address, name, date
  FROM (SELECT Tab1.code, Tab1.address, Tab2.name, Tab2.date,
               row_number() OVER (PARTITION by tab1.code 
                                  ORDER BY tab2.date DESC) rn
          FROM Tab1 
          JOIN Tab2 ON tab1.code = tab2.code)
 WHERE rn = 1

Aggregates:

SELECT Tab1.code, Tab1.address, 
       MAX(Tab2.name) KEEP (DENSE_RANK FIRST ORDER BY tab2.date DESC) name,
       MAX(tab2.date) KEEP (DENSE_RANK FIRST ORDER BY tab2.date DESC) date
  FROM Tab1 
  JOIN Tab2 ON tab1.code = tab2.code
 GROUP BY Tab1.code, Tab1.address

This will select one row for each row in table 1. If there are two rows in table 2 with the same date for the same code, one row only will be chosen arbitrarily.

Upvotes: 1

Related Questions