You have a database that contains the tables shown in the exhibit. (Refer to the Exhibit).
You need to create a query for a report. The query must meet the following requirements:
NOT use object delimiters.
Return the most recent orders first.
Use the first initial of the table as an alias.
Return the most recent order date for each customer.
Retrieve the last name of the person who placed theorder.
Return the order date in a column named MostRecentOrderDate that appears as the last column in the
report.
The solution must support the ANSI SQL-99 standard.
Which code segment should you use?
To answer, type the correct code in the answer area.
A.
Explanation:
SELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate
FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.LastName
ORDER BY O.OrderDate DESC
Hi,
I think it’s better group by the result on CustomerId, because two customers could have the same last name.
Select LastName, Max(OrderDate) as MostRecentOrderDate
From Customer as C Inner Join Orders as O
ON C.CustomerId = O.CustomerId
Group by C.CustomerId
Order by MostRecentOrderDate DESC
2
0
“Group by CustomerId” is not correct, this would result in an error Column ‘Customers.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.message”
So the submitted answer is (almost) correct, beside one little mistake: the ORDER BY clause (“ORDER BY O.OrderDate”) would also result in such an error message thats why it has to be “ORDER BY Max(OrderDate) DESC” or (better) “Order by MostRecentOrderDate DESC” (as it is in the first reply).
1
0
Correct Answer:
SELECT c.LastName
, Max(o.OrderDate) MostRecentOrderDate
FROM Sales.Orders o
JOIN Sales.Customer c ON o.CustomerID = c.CustomerID
Group by c.LastName
Order by MostRecentOrderDate DESC
0
0
The question is to select the last name.
0
0
I’m particulary agree with Naser. We cannot group by the ‘LastName’. I think the solution should be like grouping by two fields: ‘CustomerId’ and ‘LastName’.
Please correct me if needed.
0
0
i think u are right
0
0
I think the right answer should be:
SELECT C.CustomerID, C.LastName, MAX(O.OrderDate) as MostRecentOrderDate
FROM Sales.Orders O
INNER JOIN Sales.Customers C on C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
ORDER BY MostRecentOrderDate DESC
The question says to retrieve LastName and MostRecentOrderDate, but it doesn’t say, the query ONLY has to retrieve these two columns. So, I maybe the correct answer should include the CustomerID column.
0
0
I would agree with David.
We need to group by CustomerID and LastName, to avoi LastName duplicates.
PLUS
we could order by either alias or the sum() expression – both work fine
0
0
IMO:
select
max(C.LastName) as LastName,
max(aprod.dtInsertDate) as MostRecentOrderDate
from Orders as O
inner join Customers as C on (O.CustomerID=C.CustomerID)
group by O.CustomerId
order by MostRecentOrderDate desc
go
select
I agree with the fact that may be multiple LastName with differente CustomerID due the absence of unique constraint, so the proposed answer isn’t correct.
0
0
SELECT C.LastName, MAX(O.OrderDate) AS MostRecentOrderDate
FROM Customers AS C INNER JOIN Orders AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.LastName, C.CustomerID
ORDER BY MostRecentOrderDate DESC;
–CustomerID serves as tie-breaker for common lastname
0
0
@Akbar and Luiz:
Both tables are not from SALES schema. Maybe you could have implied they’re from ‘dbo’ schema, as in “FROM dbo.Customers AS c INNER JOIN dbo.Orders AS o”
0
0
SELECT c.CustomerID, c.LastName, MAX(o.OrderDate) AS MostRecentOrderDate
FROM Sales.Customers c
INNER JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.LastName
ORDER BY o.OrderDate DESC
I agree with David. And if CustomerID appeared FOLLOWING ‘SELECT’, it has to be in ‘GROUP BY’. So we need to group by c.CustomerID and c.LastName together.
0
0
The question is very confusing as it also stated “Return the most recent orders first”. Does this mean the result should contain orderid ( id does say “return orders” after all)??
0
0
— Naser is correct, but has supplied invalid code
— Unless the Customer LastNames are unique, the code would
— return incorrect values (Miss Duplicate Customer Names)
— Please use below for proof
— This Creates tables and records to prove the point
CREATE TABLE TestCustomer
(
CustomerID INT NOT NULL,
LastName varchar(100)
)
CREATE TABLE TestOrders
(
OrderID INT NOT NULL,
OrderDate datetime NOT NULL,
CustomerID INT NOT NULL
)
INSERT INTO TestCustomer (CustomerID, LastName) VALUES(1,’Smith’)
INSERT INTO TestCustomer (CustomerID, LastName) VALUES(2,’Jones’)
INSERT INTO TestCustomer (CustomerID, LastName) VALUES(3,’Smith’)
–Add 3 orders dated yesterday
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(1,Getdate()-1,1)
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(2,Getdate()-1,2)
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(3,Getdate()-1,3)
–Add 3 orders dated today
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(1,Getdate(),1)
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(2,Getdate(),2)
INSERT INTO TestOrders (OrderID, OrderDate, CustomerID) VALUES(3,Getdate(),3)
SELECT
C.LastName,
Max(O.OrderDate) AS MostRecentOrderDate
FROM TestCustomer AS C Inner Join TestOrders AS O
ON C.CustomerId = O.CustomerId
GROUP BY C.LastName
ORDER BY MostRecentOrderDate DESC
–Result
–MostRecentOrderDate for One of the “Smith” Customers is missing
–1 Smith 2017-08-23 22:52:34.340
–2 Jones 2017-08-23 22:52:34.337
SELECT
C.LastName,
Max(O.OrderDate) AS MostRecentOrderDate
FROM TestCustomer AS C Inner Join TestOrders AS O
ON C.CustomerId = O.CustomerId
GROUP BY C.CustomerID
ORDER BY MostRecentOrderDate DESC
–Result
–Msg 8120, Level 16, State 1, Line 2
–Column ‘TestCustomer.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT
C.CustomerID,
C.LastName,
Max(O.OrderDate) AS MostRecentOrderDate
FROM TestCustomer AS C Inner Join TestOrders AS O
ON C.CustomerId = O.CustomerId
GROUP BY C.CustomerID, C.LastName
ORDER BY MostRecentOrderDate DESC
–Result
–All Customers with all most Recent Order only are displayed as requested
0
0
P.S. You can remove C.CustomerID from the SELECT statement as it is in the GROUP BY, but in the real world you would not know which customer this was. The question does not say what you “shouldn’t” retrieve, just what you must retrieve. Therefore I have included it in the answer to address this shortcoming.
0
0
P.P.S use the DROP commands to cleanup after the test…
DROP TABLE TestCustomer
DROP TABLE TestOrders
0
0
Why do we need to put ORDER BY ? I don’t see the relevance according to the question.
0
0