For Oracle SQL, getting a distinct value across multiple tables and columns

Question

I have the following sample query,

select o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, l.COMPANY_ID 
from TABLE1 o, TABLE2 l 
where o.ENTRY_ID = l.ENTRY_ID 
and COMPANY_ID in (10, 11, 12, 13)

that would return a set of data similar to the following:

ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID
1, Description 1, 2/12/2008, 10
2, Description 2, 2/12/2008, 10
3, Description 3, 2/10/2008, 10
4, Description 4, 2/11/2008, 10
4, Description 4, 2/11/2008, 11
4, Description 4, 2/11/2008, 12
4, Description 4, 2/11/2008, 13

If an entry is associated with multiple companies, this query will return the same entry 1 time for each company it is associated with.

I would like the query to be distinct by COMPANY_ID, so that if an entry is associated with multiple companies, it is only returned once.

Here is the result set I want returned:

ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID
    1, Description 1, 2/12/2008, 10
    2, Description 2, 2/12/2008, 10
    3, Description 3, 2/10/2008, 10
    4, Description 4, 2/11/2008, 10

but, the following would be acceptable as well,

ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID
    1, Description 1, 2/12/2008, 10
    2, Description 2, 2/12/2008, 10
    3, Description 3, 2/10/2008, 10
    4, Description 4, 2/11/2008, 11

I want distinct ENTRY_ID. In the result set I am getting now, I get four entries with ENTRY_ID because four different companies share that entry. I want only one entry returned when the entry is shared between multiple companies.

Answer 1

SELECT ENTRY_ID, DESCRIPTION, ENTRY_DATE, COMPANY_ID
FROM (
  SELECT o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, l.COMPANY_ID,
         ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY entry_date DESC, entry_id DESC) AS rn
  FROM TABLE1 o, TABLE2 l 
  WHERE o.ENTRY_ID = l.ENTRY_ID 
    AND COMPANY_ID in (10, 11, 12, 13)
  )
WHERE rn = 1

This will return entry with the last ENTRY_DATE or a greater ENTRY_ID be there more than one entry equal to the last ENTRY_DATE

Answer 2

If you are happy to have the largest COMPANY_ID returned:

select o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, MAX( l.COMPANY_ID ) AS COMPANY_ID
from TABLE1 o, TABLE2 l 
where o.ENTRY_ID = l.ENTRY_ID 
and COMPANY_ID in (10, 11, 12, 13)
group by o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE;

Answer 3

I would like the query to be distinct by COMPANY_ID, so that if an entry is associated with multiple companies, it is only returned once.

The question is not clear, do you want distinct entries or distinct companies. Maybe you can also show the output you want so we can understand better.

If you want distinct entries and the company_id to list with it does not matter here is the query:

select * from (
select o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE, l.COMPANY_ID, row_number() over (partition by o.ENTRY_ID, o.DESCRIPTION, o.ENTRY_DATE order by l.COMPANY_ID) rn
from TABLE1 o, TABLE2 l 
where o.ENTRY_ID = l.ENTRY_ID 
and COMPANY_ID in (10, 11, 12, 13)
)
where rn=1;