0

I'm using Oracle 11G.

I've got the following table in the database: enter image description here

I'd like to get the field "Value" for each most recent Id_Number. For example, from the table above, I'd like to get the rows:

24/08 1 8

03/09 2 95

I managed to get the date and Id_Number for each most recent Id_Number using:

select max(Date), Id_Number from table group by Id_Number

But I don't see how I can get "Value". Can you guide me?

yo_haha
  • 359
  • 1
  • 4
  • 15
  • 1
    possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Bulat Sep 05 '14 at 14:16

2 Answers2

1

Here is what I consider a standard SQL solution:

SELECT * FROM table t
WHERE NOT EXISTS 
 (SELECT * FROM table WHERE t.id_number = id_number AND date > t.date)

You can look for questions with "First/Last record within a group" in title and you will find plenty other options.

Bulat
  • 6,869
  • 1
  • 29
  • 52
1

Try this:

select t1.*
  from YOUR_TABLE t1
     , (
        select min(date) dat
             , id_number 
          from YOUR_TABLE 
         group by id_number
       ) t2
 where t1.date = t2.dat
   and t1.id_number = t2.id_number
neshkeev
  • 6,280
  • 3
  • 26
  • 47