Query ... Distinct rows

I have a table as follows
ORDER_ID CODE STATUS
1000 XA3 5
1000 XA1 4
1000 XA7 5
1001 X35 5
1001 XA3 5
I want to run a query that will return the distinct ORDER_ID that is Status
= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
For example above, the result set will be 1001 only (as 1000 has one record
with Status of 4).
I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
appear to work :-(
Thanks in advance!
Wez
On Fri, 17 Jun 2005 03:30:02 -0700, Wez wrote:

> I have a table as follows
>ORDER_ID CODE STATUS
>1000 XA3 5
>1000 XA1 4
>1000 XA7 5
>1001 X35 5
>1001 XA3 5
>I want to run a query that will return the distinct ORDER_ID that is Status
>= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
>For example above, the result set will be 1001 only (as 1000 has one record
>with Status of 4).
>I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
>appear to work :-(
>Thanks in advance!
>Wez
Hi Wez,
This one should work, actually:
SELECT Order_ID
FROM YourTable
GROUP BY Order_ID
HAVING MIN(Status) = 5 AND MAX(Status) = 5
What eexactly does "doesn't appear to work" mean? Error messages? Wrong
results? Blue smoke in the server room? It's hard to help you without
knowing what's happening!
BTW, here's another query that should also work:
SELECT DISTINCT t1.Order_ID
FROM YourTable AS t1
WHERE NOT EXISTS (SELECT *
FROM YourTable AS t2
WHERE t2.Order_ID = t1.Order_ID
AND t2.Status <> 5)
/* Adding the line below might improve performance
AND t1.Status = 5
*/
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
when i say didnt work I mean I was getting wrong results i.e. orders
appearing in the result set that had lines not yet equal to status '5'.
However your alternative method has worked well :-)
Thanks,
Wez