SQL Query structure

I have a table called Orders Like So:

OrderItemID          CustomerIDNumber          OrderProcessed

1                    9212060068089             False
2                    6412180017080             False
3                    9212060068089             False
4                    5508245127003             False
5                    9212060068089             False

      

What I would like to do, and it seems to me that I am really struggling with the correct one, is to select all unique client ID numbers where the processed request is false.

Now this is easy to do using DISTINCT, but the TRICKY PART I'm struggling with is that I WANT the customer ID numbers that still retain their order in terms of the order ID. So the sample output should be:

CustomerIDNumber          OrderProcessed

9212060068089             False
6412180017080             False
5508245127003             False

      

PLEASE NOTE: ALL VALUES IN THE ORDERING PROCESS ARE EASY HERE, VERY IN MY TABLE, THERE WILL BE SOME TRUE VALUES FOR PROCESSING THE ORDER.

Here is the WRONG OUTPUT I am currently getting:

CustomerIDNumber          OrderProcessed

5508245127003             False
6412180017080             False
9212060068089             False

      

As you can see, it sorts the customer ID in ascending order, which is NOT what I want.

+3


source to share


2 answers


You can use ROW_NUMBER

:



;WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY CustomerIDNumber ORDER BY OrderItemID)
    FROM dbo.Orders
    WHERE OrderProcessed = 'False'
)
SELECT CustomerIDNumber, OrderProcessed
FROM CTE
WHERE RN = 1
ORDER BY OrderItemID

      

+3


source


How about grouping and ordering?

SELECT CustomerIDNumber, max(OrderProcessed)
FROM dbo.Orders
WHERE OrderProcessed = 'False'--or however you're implementing your boolean here
GROUP BY CustomerIDNUmber
ORDER BY min(OrderItemID)

      



Sql Fiddle Demo

+5


source







All Articles