Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > SQL SERVER (TECHNET) > Query ... Distinct rows
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 25th July 07:19
wezgv&r
External User
 
Posts: 1
Default 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
  Reply With Quote


  sponsored links


2 25th July 07:20
hugo kornelis
External User
 
Posts: 1
Default Query ... Distinct rows


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)
  Reply With Quote
3 25th July 07:20
wezgv&r
External User
 
Posts: 1
Default Query ... Distinct rows


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
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666