Geek asks and answers

Select orders from customers for the first time

I need help creating an SQL query that returns orders from customers who only ordered once.

Tables and corresponding fields are as follows:

Order        Customer       
-------      -----------     
orderId      customerId    
orderDate     
customerId     
etc.

I am looking for a result set of order records where there is only one customer identifier. For the next data set ...

[orderId]    [customerId]     [orderDate]    [etc.]
----------   ------------   ------------   ------------
o1           c1                 1/1/14        foo
o2           c2                 1/1/14        baz
o3           c3                 1/3/14        bar
o4           c2                 1/3/14        wibble

I would like the results to be

[orderId]    [orderDate]    [etc.]
---------    -----------    ------
o1           1/1/14         foo 
o3           1/3/14         bar

Orders o2 and o4 are omitted since c2 ordered twice.

Any help would be greatly appreciated.

Sorry, did not post a failed attempt. This is what I tried ...

SELECT customerId,
       orderId,
       orderDate,
       Count(*)
FROM   Orders
GROUP  BY orderId,
          orderDate,
          customerID
HAVING Count(*) = 1
ORDER  BY orderId

It seems he is returning all orders.

+3
source share
2 answers

Try the following (assuming SQL Server 2005 +):

;WITH CTE AS
(
    SELECT  *,
            N = COUNT(*) OVER(PARTITION BY customerId)
    FROM Orders
)
SELECT *
FROM CTE
WHERE N = 1

CTE, , ( OVER, SQL Server 2005 +):

SELECT *
FROM (  SELECT  *,
                N = COUNT(*) OVER(PARTITION BY customerId)
        FROM Orders) T
WHERE N = 1

(, , SQL Server Server 2005 ), GROUP BY / HAVING COUNT(*)=1 , Orders ( ):

SELECT o.*
FROM Orders o
  JOIN
    ( SELECT customerId
      FROM Orders
      GROUP BY customerId
      HAVING COUNT(*) = 1
    ) c
    ON c.customerId = o.customerId ;

NOT EXISTS ( COUNT(), MySQL):

SELECT o.*
FROM Orders o
WHERE NOT EXISTS
    ( SELECT 1
      FROM Orders c
      WHERE c.customerId = o.customerId 
        AND c.orderId <> o.orderId
    ) ;
+14

ORDERS , .

SELECT [customerID],
       MIN([orderId])   AS [orderId],
       MIN([orderDate]) AS [orderDate],
       MIN([etc.])      AS [etc.]
FROM   [Orders]
GROUP  BY [customerID]
HAVING Count(*) = 1
ORDER  BY [customerID]

, , MIN/MAX.

, , . , , ( , BIT, XML)

+2

More articles:


All Articles