Select distinct by one column in Oracle but displaying multiple columns too

I have this query:

    select DISTINCT cpc.EXTRACT_ID, cpc.OS_ID,...,cpvi.VOYAGE_STRT_DT, 
cpob.FISCAL_MONTH_START_DT from CPOB_PROCESS_CTRL cpc
    inner join CPOB_PLY_OUTSTD_BAL cpob on cpc.BRAND_NAME
 = cpob.BRAND_ID and cpc.SHIP_NAME = cpob.SHIP_NAME
    inner join CPOB_VOYAGE_INFO cpvi on cpc.VOYAGE_ID = cpvi.VOYAGE_ID

The problem is that I want the column cpc.EXTRACT_ID to not be duplicated on the results. When I just select cpc.EXTRACT_ID on my query the results are accurate and the query doesn’t contain duplicated results for cpc.EXTRACT_ID

   select DISTINCT cpc.EXTRACT_ID from CPOB_PROCESS_CTRL cpc
        inner join CPOB_PLY_OUTSTD_BAL cpob on cpc.BRAND_NAME
     = cpob.BRAND_ID and cpc.SHIP_NAME = cpob.SHIP_NAME
        inner join CPOB_VOYAGE_INFO cpvi on cpc.VOYAGE_ID = cpvi.VOYAGE_ID

But I need in the results the other fields too, I don’t care about the other fields to be duplicated or not, just cpc.EXTRACT_ID. How can I change the first script to have desired results?

Answer

If you don’t care about other columns, aggregate them, e.g.

select cpc.EXTRACT_ID, 
       max(cpc.OS_ID) os_id,
       max(cpvi.VOYAGE_STRT_DT) voyage_strt_dt,
       max(cpob.FISCAL_MONTH_START_DT) fiscal_month_start_dt
from ...
group by cpc.extract_id

If that’s not what you are looking for, please, post some sample data and desired result. That might help us help you.

Advertisement

Answer