Re: Query ... Distinct rows

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)
.

Relevant Pages

  • Re: Comparing Data between Tables
    ... What is a query that would allow me to look at the data ... Make sure that the column chossen for ColumnX does NOT allow NULLs. ... Best, Hugo ... Prev by Date: ...
    (microsoft.public.sqlserver.mseq)
  • Re: need some SQL help
    ... hugo, do you know how I'd modify your query to return only a count of the ... Prev by Date: ...
    (comp.databases.theory)
  • Stored Procedure - Obtaining Output
    ... I would like to set up a stored procedure that does the following... ... How to I trap the output of this query! ... Wez ... Prev by Date: ...
    (microsoft.public.sqlserver.mseq)
  • Re: query plan in clear text
    ... you just have to create a query that does so. ... Please post DDL and all statements. ... > Best, Hugo ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: Nearest Common Ancestor Report (XDb1s $1000 Challenge)
    ... Hugo Kornelis wrote: ... >>And then for a 10 million record database. ... >>this query would run, please. ... I don't have the money to spare on the kind of hardware that would be ...
    (comp.object)