Home 2019 August SQL Puzzle: SQL Advance Query – Find the Order basis on thier Status and Step

SQL Puzzle: SQL Advance Query – Find the Order basis on thier Status and Step

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and require output data to find order which step is 0 with status D and for the same order other status are P.

Input Data:

Expected Output:

Create a table with sample data:

Solution 1: Using CTE

Solution 2: Using GROUP BY .. Having

Please try the different solutions for this puzzle and share it via comment...

Anvesh Patel
SQL Server: How to compare your objects between two DatabasesPostgreSQL: How to Delete all duplicate rows Except one
Comments: 19
  1. Harbeer Kadian
    September 9, 2019 at 10:04 am

    select distinct orderid
    from tbl_orders where orderid in (select orderid from tbl_orders where step = 0 and status = ‘D’)
    group by orderid, status
    having status = ‘P’

    ReplyCancel
  2. Pavan Shetty
    October 22, 2019 at 8:50 am

    ;WITH CTE
    AS
    (
    SELECT * FROM TBL_ORDERS
    WHERE Status=’D’ AND STEP=0
    )
    SELECT DISTINCT ORDERID FROM CTE C WHERE EXISTS (SELECT 1 FROM TBL_ORDERS T WHERE C.ORDERID=T.ORDERID AND T.STATUS=’P’ )

    ReplyCancel
  3. n
    October 22, 2019 at 8:54 am

    Hi
    I think the first solution and also Harbeer Kadian solution in the comments are not good.
    In case for example we have an additional record such as (‘ABC’, 4, ‘D’)
    this solutions will not work
    only Solution 2: Using GROUP BY .. Having is good

    ReplyCancel
  4. Samrat
    October 24, 2019 at 4:25 am

    SELECT DISTINCT OrderID
    FROM #TEMP O
    WHERE Step=0 AND Status=’D’
    AND EXISTS(SELECT 1 FROM #TEMP I WHERE I.OrderID=O.OrderID AND I.Status = ‘P’)

    ReplyCancel
  5. koustav
    October 25, 2019 at 10:55 am

    select orderid from report.tbl_Orders t1 where step=0 and status=’D’
    and exists
    (select 1 from report.tbl_Orders where orderid=t1.orderid and status =’P’)

    ReplyCancel
  6. HARISH
    November 5, 2019 at 2:51 am

    –USING SUBQUERY WITH RANKING FUNCTION
    SELECT ORDERID FROM
    (
    SELECT ORDERID, ROW_NUMBER() OVER(ORDER BY ORDERID) AS ROWNUMBER
    FROM tbl_Orders
    )RESULT
    WHERE ROWNUMBER = 1

    ReplyCancel
  7. Ajay
    November 7, 2019 at 9:14 am

    select orderid from tbl_Orders where step=0 and status=’d’and orderid=’abc’

    ReplyCancel
  8. AK
    November 9, 2019 at 6:08 pm

    WITH cte ([OrderID], Step, Status) AS
    (SELECT [OrderID], Step, [Status] FROM tbl_Orders
    WHERE Step = 0 and [Status] = ‘D’)

    SELECT DISTINCT OrderId FROM tbl_Orders
    WHERE [Status] = ‘P’ and OrderID IN (SELECT orderid from cte)

    ReplyCancel
  9. karthick
    December 2, 2019 at 7:28 pm

    select distinct orderid from orders a
    where a.orderid in (select b.orderid from orders b where status=’p’)

    ReplyCancel
  10. R karthick
    December 3, 2019 at 6:38 pm

    select orderid from orders
    group by orderid
    having count(*) >2

    ReplyCancel
  11. Naveed Anjum
    December 16, 2019 at 12:37 pm

    Hi Anvesh,

    Both solution will only work on given data, but if I add one more status (lets say ‘X’) then “group by” method won’t work.

    suppose i add one more record like below
    INSERT INTO tbl_Orders VALUES (‘ABC’, 4, ‘X’)

    then it “Group by” method will not work,

    while, CTE will work.

    ReplyCancel
  12. Noam
    December 31, 2019 at 6:19 pm

    SELECT orderid
    FROM tbl_orders o
    WHERE step = 0
    AND status = ‘D’
    AND EXISTS
    (
    SELECT 1
    FROM tbl_orders o2
    WHERE o.orderid = o2.orderid
    AND o2.status = ‘P’
    )

    ReplyCancel
  13. Hareesh
    January 2, 2020 at 4:00 pm

    select OrderID from tbl_Orders
    where (Step = 0 and Status = ‘D’) and
    OrderID in
    (select distinct OrderID
    from tbl_Orders
    where Status = ‘P’)

    ReplyCancel
  14. Sri
    February 13, 2020 at 12:08 pm

    SELECT DISTINCT A.OrderID
    FROM(
    select OrderID
    from tbl_Orders a
    where step=0 and status=’D’
    ) A JOIN
    (select OrderID
    from tbl_Orders a
    where status=’P’
    ) B
    ON A.OrderID=B.OrderID

    ReplyCancel
  15. Krupa
    March 11, 2020 at 2:39 pm

    SELECT OrderID FROM tbl_Orders As A where Step =0 AND Status = ‘D’ AND OrderID IN (SELECT B.ORDERID FROM tbl_Orders AS B where B.Status = ‘P’ AND A.OrderID = B.OrderID)

    ReplyCancel
  16. Amit Kumar
    March 21, 2020 at 8:49 am

    SELECT ORDERID FROM tbl_Orders
    GROUP BY ORDERID,step
    HAVING STEP= (SELECT COUNT(ORDERID) FROM tbl_orders
    WHERE STATUS= ‘D’ AND STEP =0)

    ReplyCancel
  17. AMIT KUMAR
    March 21, 2020 at 8:51 am

    SELECT ORDERID FROM tbl_Orders
    GROUP BY ORDERID ,STEP
    HAVING STEP= (SELECT COUNT(ORDERID) FROM tbl_orders
    WHERE STATUS= ‘D’ AND STEP =0)

    ReplyCancel
  18. surekha
    April 22, 2020 at 10:44 am

    Select distinct(OrderId) from tbl_Orders
    Where Orderid in (
    Select OrderId from tbl_Orders
    Where step=0 and Status=’D’)
    and status=’P’

    ReplyCancel
  19. Pranay Bhoyar
    May 10, 2020 at 2:33 pm

    SELECT OrderID FROM #tbl_Orders
    WHERE Step=0 AND Status=’D’
    INTERSECT
    SELECT OrderID FROM #tbl_Orders
    WHERE Step0 AND Status=’P’

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

August 12, 2019 19 Comments SQL Puzzle, , , , , , , , , ,