CODEWITHSUNDEEP
sqlsql-server
Curiosity
Curiosity

Reputation: 111

SQL Query Statement 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'd like to do, which I seem to be really struggling with getting correct, is to Select all unique Customer ID Numbers where the Order Processed is False.

Now that is simple to do, by using DISTINCT, but the TRICKY PART that I'm struggling with, is that I WANT the Customer ID Numbers to still maintain their order in terms of the Order Item ID. So example output should be:

CustomerIDNumber          OrderProcessed

9212060068089             False
6412180017080             False
5508245127003             False

TAKE NOTE: ALL VALUES IN ORDER PROCESSED ARE FALSE HERE, OBVIOUSLY IN MY TABLE, THERE WILL BE SOME TRUE VALUES FOR ORDER PROCESSED.

Here is the WRONG OUTPUT that I'm currently getting:

CustomerIDNumber          OrderProcessed

5508245127003             False
6412180017080             False
9212060068089             False

As can be seen, it is sorting the Customer ID Number in ascending order, which IS NOT what I want.

Upvotes: 3

Views: 59

Answers (2)

beercodebeer
beercodebeer

Reputation: 990

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

Upvotes: 5

Lamak
Lamak

Reputation: 70638

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

Upvotes: 3

Related Questions