0

I have a table like this:

ID  DATE        TableName  
X   29-Jan-12   A  
X   29-Feb-12   B  
X   29-Jan-12   C  
Y   18-Nov-12   A  
Z   18-Nov-14   C  
X   17-Nov-13   D  
Z   14-May-12   A  
Y   18-Nov-13   D

I want to fetch unique ID with MaxDate so that output should be like this- 

ID  DATE    TableName  
Z   18-Nov-14   C  
Y   18-Nov-13   D  
X   17-Nov-13   D

I am using select ID, MAX(DATE), TableName from table but that is not giving me the desired output.

Barmar
  • 741,623
  • 53
  • 500
  • 612
gaurav bharadwaj
  • 1,669
  • 1
  • 12
  • 29
  • What is the RDBMS you are using? – SS_DBA Dec 02 '16 at 18:20
  • Lesson here: If you don't choose the correct DB product, someone who is an expert in another product may close your question. @Barmar closed the question and linked to an older one that is specific to MySQL; none of the answers that exist in Oracle (for example) but not in MySQL are shown in that older question, but who cares. Not Barmar, clearly. –  Dec 02 '16 at 18:47
  • Here's an Oracle duplicate: http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column?rq=1 – Barmar Dec 02 '16 at 18:53
  • There are numerous other similar questions listed in the **Related** sidebar. – Barmar Dec 02 '16 at 18:53
  • @Barmar - the lesson for the OP remains. Always pay attention to tags, don't just use the defaults SO places there for you. –  Dec 02 '16 at 18:53
  • I also suspect the MySQL solutions will work in Oracle and Sybase. – Barmar Dec 02 '16 at 18:54
  • @Barmar - lol at the linked answers for Oracle. I did find the most efficient answer (eventually), which is Oracle specific with no analog in MySQL - the answer I gave in this thread. It took me about three times as long to find that answer in the older thread than it took me to write the full answer here. Another problem with old threads (that one is eight years old) is that at least in Oracle, new features, optimizations etc. are added at least every 2-3 years, so many "better" solution can't possibly be found in old threads. That's a problem with the whole concept of "duplicate questions". –  Dec 02 '16 at 19:06
  • @mathguy The OP still hasn't answered which database he's actually using, so who knows if that answer is even relevant. A generic solution that works in any database should be an acceptable answer, even if it's not optimal for a specific DB. – Barmar Dec 02 '16 at 19:08

2 Answers2

1

You can use a in clause on a subselect group by If you are using a db that allow tuple 

select id, date, tableName 
from my_table 
where (id, date) in (select id, max(date) 
                     from my_table 
                     group by id);

or if the db don't allow tuple you can use an inner join 

select id, date, tableName 
from my_table a 
inner join ( select id, max(date) max_date 
                     from my_table 
                     group by id) t on t.id = a.id and  t.max_date = a.date;
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
1

Oracle answer:

What you are missing is picking up the tablename that corresponds to the max(date) within each group.

That is done most efficiently with keep (dense_rank last...) like so: (note - DATE is a reserved word in Oracle so I hope your column name is not DATE and you used that just as a placeholder...)

select   id, max(date) as date, 
         max(tablename) keep (dense_rank last order by date) as tablename
from     table_like_this
group by id;

Oracle documentation (for FIRST but read the text, it refers also to LAST) https://docs.oracle.com/cloud/latest/db112/SQLRF/functions065.htm#SQLRF00641