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

see [https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group/14346780](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group/14346780) — Stu, May 07 '22 at 07:59
@Stu thank you but this can be used for the **latest date** case only... — Umut K, May 07 '22 at 08:08
@Stu in my dBase, I assume where PRODUCT_COST_STATUS = 1 is the latest date, therefore I can use this code but it is not the exact answer actually if there are some data where PRODUCT_COST_STATUS = 1 is the latest date. **WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn FROM DocumentStatusLogs ) SELECT * FROM cte WHERE rn = 1** — Umut K, May 07 '22 at 08:16
With your rep, you should know better than to share your sample data as image, please show any sample data and expected outcome as text in stead of an image, thank you — GuidoG, May 07 '22 at 08:21
There is simply no reason to use GROUP BY in an EXISTS clause. Here you don't even use an AGGREGATE - meaning the GROUP BY is the same as DISTINCT. The engine is optimized to stop searching when it encounters the first row - neither GROUP BY nor DISTINCT do anything useful. Compare the posted query here to that in your answer - poor formatting makes the code difficult to read and will discourage many from helping. Two good lessons to learn. — SMor, May 07 '22 at 11:29
@SMor thank you for your time and effort to comment. As coding is not my first profession, I am trying to improve it, that's why I am here :) For the formatting, I am using the website to adjust the formatting. https://www.dpriver.com/pp/sqlformat.htm If you can suggest any other website, I would love to use it. — Umut K, May 09 '22 at 05:31

score 0 · Answer 1 · answered May 07 '22 at 09:40

0

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

answered May 07 '22 at 09:40

Umut K

1,364
12
25

Now let's try learning and practicing some best practices. Define a short by not cryptic alias for each table and use it when referencing every column. GROUP BY tends to produce a sorted resultset but that is not guaranteed. Most every query should have an ORDER BY clause. Use statement terminators. Don't use square brackets if not needed. Do write consistent code. – SMor May 07 '22 at 11:37

Prefered results

1 Answers1