• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 37
  • Last Modified:

select max date from joined tables with columns with unique values

Hi I am at my wits end!

I want to get the most recent date from joined tables with unique values in 2 columns. How do I do this? I have also tried the ranking (but John has the same rank) and tried rownum = 1, but I still get the same results below for some reason
  1. Name     ID            Email                  DeptNum      DeptScore      OnDate
  2. John     A46       john@doe.com            100                  50                  5/11/2011
  3. John     A46       johndoe@aol.com      200                  75                   7/21/2015
  4. Alice    B33       alice@hotmail.com      100                  50                  4/15/2014

I want to get the following:
  1. Name     ID            Email                  DeptNum      DeptScore      OnDate
  2. John     A46       johndoe@aol.com      200                  75                   7/21/2015
  3. Alice    B33       alice@hotmail.com      100                  50                  4/15/2014

My query (oracle)
select distinct e.name, e.id, e.email, d.deptnum, d.deptscore, d.ondate
from  emp e
inner join dept d on d.deptnum = e.dnum
    and d.ondate = e.livedate
    and d.ondate = (select max(m.ondate) from dept m where d.ondate = m.ondate)
--where e.id in ('A46','B33')

Open in new window

Ramanth Lex
Ramanth Lex
1 Solution
How about this?

SELECT name,
  FROM (SELECT e.name,
               ROW_NUMBER() OVER(PARTITION BY name, id ORDER BY ondate DESC) rn
          FROM emp e INNER JOIN dept d ON d.deptnum = e.dnum AND d.ondate = e.livedate)
 WHERE rn = 1

Open in new window

If it doesn't produce the desired results, please post data for emp and dept (each table, not the results of a join) that produce wrong results.
Ramanth LexAuthor Commented:
Thank you, good sir!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now