CODEWITHSUNDEEP
sql-server-2012
Zac Davidson
Zac Davidson

Reputation: 247

SQL Server 2012 Query Confusion

I am a beginner with SQL and I cannot seem to come up with a correct query for this question:

Use a correlated subquery to return one row per customer, representing the customer’s oldest order (the one with the earliest date). Each row should include these three columns: EmailAddress, OrderID, and OrderDate.

I started by joining orders and customers table. EmailAddress is the only column needed from customers table.

      SELECT EmailAddress, OrderDate, orderID
      FROM Customers c JOIN orders o
      ON c.CustomerID = o.CustomerID

Upvotes: 2

Views: 2833

Answers (3)

Todd Twiggs
Todd Twiggs

Reputation: 61

A less complicated answer:

SELECT  EmailAddress, OrderID, OrderDate AS OldestOrder
  FROM  Customers AS C
        JOIN Orders AS O1
            ON C.CustomerID = O1.CustomerID
 WHERE  O1.OrderDate =
            (SELECT MIN(OrderDate)
             FROM Orders AS O2
             WHERE C.CustomerID = O2.CustomerID)

Upvotes: 6

valerywandah
valerywandah

Reputation: 11

You can still use HAVING Clause and the IN Operator to achieve same results for this particular problem

Code: 

SELECT DISTINCT EmailAddress, OrderID, OrderDate
FROM Customers
    JOIN orders
    ON Customers.CustomerID = Orders.CustomerID
GROUP BY EmailAddress, OrderID, OrderDate
HAVING OrderID IN (1,2,4,5,6,7,8)
ORDER BY EmailAddress ASC;

Upvotes: 1

Sarath Subramanian
Sarath Subramanian

Reputation: 21301

Use ROW_NUMBER() to get unique ids for each customer which orders by OrderDate in descending order. And the latest date will be RNO=1. Now do the filtration in outer query.

SELECT EmailAddress, OrderDate, orderID
FROM
(
   SELECT ROW_NUMBER() OVER(PARTITION BY c.CustomerID ORDER BY OrderDate DESC)RNO,
   EmailAddress, OrderDate, orderID
   FROM Customers c JOIN orders o
   ON c.CustomerID = o.CustomerID
)TAB 
WHERE RNO=1

Upvotes: 1

Related Questions