Prefered results

Question

I am trying to list all DEPARTMENT_IDs with PRODUCT_IDs, first where PRODUCT_COST_STATUS = 1 but there are also data with where PRODUCT_COST_STATUS = 0. I prefer to list first "1"s and if not then "0"s with the latest date (this is another case for future) ... The code I wrote should give the expected result but it takes a lot of time to run query. I don't to want to list duplicate DEPARTMENT_ID.

Is there any way around ?

Thanks

 SELECT  PRODUCT_ID
    ,PRODUCT_COST_STATUS
    ,DEPARTMENT_ID FROM   [PRODUCT_COST] PC
WHERE  PRODUCT_COST_STATUS = 1
    OR PRODUCT_ID NOT IN (SELECT PRODUCT_ID
                          FROM
       [PRODUCT_COST]
       
                          where PRODUCT_COST_STATUS = 0
                          GROUP  BY PRODUCT_ID,
                                    PRODUCT_COST_STATUS,
                                    DEPARTMENT_ID) GROUP  BY PRODUCT_ID,
      PRODUCT_COST_STATUS,
      DEPARTMENT_ID
ORDER  BY PRODUCT_ID,
      DEPARTMENT_ID  

Answer 1

I solved with the help of my friend and wanted to share here...

PRODUCT_COST_STATUS is a bit

SELECT   PRODUCT_ID
        ,DEPARTMENT_ID
        ,LOCATION_ID
        ,max(cast(PRODUCT_COST_STATUS as int)) as maxpcs
        ,max(ACTION_DATE) as maxad
FROM    [PRODUCT_COST]
group by 
PRODUCT_ID, DEPARTMENT_ID,LOCATION_ID