prog_prog prog_prog - 1 year ago 127
SQL Question

selecting by date - oracle

I did a complex query from several tables, that return me a list of entities by their id.

the query build like:

"with t as ( --the complex query --) select * from t"

like (it doesn't really matter) :




I also have table that contain 3 columns:

  1. sequence (used me also as identity in further)

  2. entity (from above)

  3. value_date (format: dd/mm/yyyy)

the value date it's not the same for all entity.

For example, the table look like that:

seq entity value_date
---------- ---------- ----------
1580 12001 30/06/2016
1579 12004 31/05/2016
1578 15003 30/06/2016
1577 12001 31/05/2016
1576 12004 30/06/2016
1575 15003 31/05/2016
1574 12004 30/04/2016
1573 67677 30/04/2016

I need to run for each entity (from the query, because the table hold also irrelevant entities for me), and select the sequence with the max value_date for the entities.

The result needs to be, list with 3 columns: seq, entity, value_date (the max per entity)

How can I do it?

I hope my question is clear.

Answer Source

Seems you need a select where max(value_date)

for each entity

select seq,  entity, value_date 
from my_table 
where (entity, value_date) in  (select entity , max(value_date) 
                               from my_table  
                               group by entity);

if useful try also this way

select a.seq,  a.entity, a.value_date 
from my_table a
inner join my_table b on a.entity = b.entity 
group by b.entity
having  a.value_date = max(b.value_date)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download