How to use max function on oracle SQL based on below data?

Question

Assuming I have following data and I wanted to collect the most recent date info's, what would be the best way to write sql using max function or any other function? FYI - I only have a read only access to the server and cannot create temp table or what so ever.

Thanks.

                 <table border="1">
             <tr>
             <th>NAME</th>
             <th>ID</th>
                 <th>CODE</th>
             <th>DATE</th>
             </tr>
             <tr>
             <td>A  </td>
             <td>Z1 </td>
             <td>780.52 </td>
             <td>11/14/11</td>
             </tr>  
             <tr>
             <td>A  </td>
             <td>Z1 </td>
             <td>780.54 </td>
             <td>10/31/11</td>
             </tr>  
             <tr>
             <td>A  </td>
             <td>Z1 </td>
             <td>780.54 </td>
             <td>10/24/11 </td>
             </tr>  
             <tr>
             <td>AB     </td>
             <td>Z22    </td>
             <td>327.23 </td>
             <td>12/09/11</td>
             </tr>  
             <tr>
             <td>AB     </td>
             <td>Z22    </td>
             <td>327.23 </td>
             <td>11/02/11</td>
             </tr>  
             <tr>
             <td>AB     </td>
             <td>Z22    </td>
             <td>327.23 </td>
             <td>09/13/11</td>
             </tr>  
             <tr>
             <td>B  </td>
             <td>Z55    </td>
             <td>327.23 </td>
             <td>01/06/11</td>
             </tr>  
             <tr>
             <td>C  </td>
             <td>Z77    </td>
             <td>327.23 </td>
             <td>01/04/11</td>
             </tr>  
             <tr>
             <td>DD </td>
             <td>Z888   </td>
             <td>327.23 </td>
             <td>10/31/11</td>
             </tr>  
             <tr>
             <td>DD </td>
             <td>Z888   </td>
             <td>327.23 </td>
             <td>10/24/11</td>
             </tr>  
             <tr>
             <td>DD </td>
             <td>Z888   </td>
             <td>327.23 </td>
             <td>10/06/11</td>
             </tr>  
             <tr>
             <td>DD </td>
             <td>Z888   </td>
             <td>327.23 </td>
             <td>08/08/11</td>
             </tr>  
             <tr>
             <td>DD </td>
             <td>Z888   </td>
             <td>780.54 </td>
             <td>07/28/11</td>
             </tr>  
             <tr>
             <td>DD </td>
             <td>Z888   </td>
             <td>327.23 </td>
             <td>07/19/11</td>
             </tr>  
             <tr>
             <td>EE </td>
             <td>Z2323  </td>
             <td>327.23 </td>
             <td>03/17/11</td>
             </tr>  
             <tr>
             <td>EE </td>
             <td>Z2323  </td>
             <td>327.23 </td>
             <td>02/24/11</td>
             </tr>  
             <tr>
             <td>EE </td>
             <td>Z2323  </td>
             <td>780.54 </td>
             <td>02/13/11</td>
             </tr>  
             <tr>
             <td>FF </td>
             <td>Z99    </td>
             <td>327.23 </td>
             <td>07/07/11</td>
             </tr>  
             <tr>
             <td>FF </td>
             <td>Z99    </td>
             <td>780.54 </td>
             <td>06/28/11</td>
             </tr>  
         <tr>
             <td>II </td>
             <td>Z963   </td>
             <td>327.23 </td>
             <td>09/19/11</td>
             </tr>  
             <tr>
             <td>II </td>
             <td>Z963   </td>
             <td>327.23 </td>
             <td>08/30/11</td>
             </tr>  
             <tr>
             <td>II </td>
             <td>Z963   </td>
             <td>327.23 </td>
             <td>06/29/11</td>
             </tr>  
             <tr>
             <td>II </td>
             <td>Z963   </td>
             <td>780.54 </td>
             <td>06/29/11</td>
             </tr>  
             <tr>
             <td>II </td>
             <td>Z963   </td>
             <td>780.54 </td>
             <td>06/14/11</td>
             </tr>  
         <tr>
         <td>L  </td>
         <td>Z99999 </td>
         <td>327.23 </td>
         <td>09/16/11</td>
         </tr>  
         <tr>
             <td>NN </td>
         <td>Z9870  </td>
         <td>327.23 </td>
         <td>11/23/11</td>
         </tr>  
         <tr>
         <td>NN </td>
         <td>Z9870  </td>
         <td>327.23 </td>
         <td>10/06/11</td>
         </tr>  
         <tr>
         <td>NN </td>
         <td>Z9870  </td>
         <td>327.23 </td>
         <td>06/07/11</td>
         </tr>  
         <tr>
         <td>NN </td>
         <td>Z9870  </td>
         <td>780.54 </td>
         <td>01/18/11</td>
         </tr>  
         <tr>
         <td>NN </td>
         <td>Z9870  </td>
         <td>780.54 </td>
         <td>01/11/11</td>
         </tr>
         </table>

Most recent per what? Name? ID Overall? Top 1? Top 10? Do you need the code too or just the data? Please add expected output for the above data. — D Stanley, Nov 26 '13 at 15:36
@OGHaza Umm yeah, I see that. What I _don't_ see is any expected output. I'm assuming `SELECT TOP 1 date FROM table` is not the right answer. — D Stanley, Nov 26 '13 at 15:56
possible duplicate of [How to get the last row in a table - Oracle 11g?](http://stackoverflow.com/questions/12378424/how-to-get-the-last-row-in-a-table-oracle-11g) — Ben, Nov 26 '13 at 16:17

Gayathri · Answer 1 · 2013-11-26T16:27:22.833

1

I think you can try any of these that should help:

select * from (Select * from your_table order by date desc) where rownum=1;
select * FROM your_table WHERE date= (SELECT MAX(date) from your_table)
SELECT * FROM (select a.*, MAX(date) OVER() max_date_column FROM your_table a) WHERE your_date_column = max_date_column
SELECT * FROM( select a.*, ROW_NUMBER OVER(ORDER BY date DESC) rn FROM your_table a) WHERE rn = 1

edited Nov 26 '13 at 16:27

answered Nov 26 '13 at 15:57

Gayathri

894
6
19

If you have Id as primary key then you can try, select * from tablename where id in (Select id from tablename order by date desc) and rownum=1; – Gayathri Nov 26 '13 at 16:02

score 1 · Answer 2 · answered Nov 26 '13 at 20:22

SQL Fiddle

Query 1:

SELECT  MIN( NAME ) KEEP ( DENSE_RANK LAST ORDER BY "DATE" ASC ) AS NAME,
        MIN( ID   ) KEEP ( DENSE_RANK LAST ORDER BY "DATE" ASC ) AS ID,
        MIN( CODE ) KEEP ( DENSE_RANK LAST ORDER BY "DATE" ASC ) AS CODE,
        MIN("DATE") KEEP ( DENSE_RANK LAST ORDER BY "DATE" ASC ) AS "DATE"
FROM tbl

Results:

| NAME |   ID |   CODE |                            DATE |
|------|------|--------|---------------------------------|
|  AB  | Z22  | 327.23 | December, 09 2011 00:00:00+0000 |

Michael B. · Accepted Answer · 2013-11-26T16:56:38.153

0

SELECT tbl.Name, tbl.Id, tbl.Code, tbl.date
FROM tbl
  JOIN (
      SELECT Name, Id, Code, MAX(date) AS Max_Date
      FROM tbl
      GROUP BY Name, Id, Code) tbl_max
    ON tbl.Name=tbl_max.Namne
      AND tbl.Id=tbl_max.Id
      And tbl.Code=tbl_Max.Code
      AND tbl.Date=tbl_max.Max_Date

edited Nov 26 '13 at 16:56

answered Nov 26 '13 at 16:04

Michael B.

61
3

Actually I want the most recent record for each name, id, and code. – user3037020 Nov 26 '13 at 16:36
I have edited the answer to give most recent record by Name, Id, and Code instead of just Id. – Michael B. Nov 26 '13 at 16:57

How to use max function on oracle SQL based on below data?

3 Answers3