select orders from first time customers

select, sql, sql-server

Solution

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

Since sometimes a pedestrian approach is preferred over complex CTEs, you can use a derived table if you want (but since it's using the `OVER` clause, you'll still need SQL Server 2005+):

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

Alternatively (if for example you are in an older than 2005 version of SQL-Server), you can use the `GROUP BY / HAVING COUNT(*)=1` method to find customers with only 1 order and then join back to the `Orders` table (no need for aggregate functions in all the columns):

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

or use `NOT EXISTS` (no `COUNT()` needed and it works even in MySQL):

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

Problem

I need help building a SQL query that returns orders from customers who have only ordered once. The tables and relevant fields are as follows: ``` Order Customer ------- ----------- orderId customerId orderDate customerId etc. ``` I'm looking for a result set of Order records where there is only one occurence of the customer id. For the following 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 ommitted because c2 has ordered twice. Any help would be greatly appreciated. Sorry, didn't put my 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 appears to return all the orders.

Original source