I am working on some practice interview Questions and am struggling with this:

You are working with a company that sells goods to customers, and they'd like to keep track of the unique items each customer has bought. The database is composed of two tables: Customers and Orders. The two table schemas are given below. We want to know what unique items were purchased by a specific customer, Wilbur, and when they were purchased. What is the correct query that returns the customer first name, item purchased, and purchase date with recent purchases first? Tables: https://imgur.com/a/D47R1KU

My answer so far is

However I am getting an incorrect message as its Printing wilbur,oranges,2019-06-10 and wilbur,oranges,2018-06-10 instead of just the one with the more recent date. Please see the picture for the two tables referenced by the question. Thanks!

CodePudding user response：

Between the where clause and ORDER BY, try:

GROUP BY FirstName, Item

And to get the most recent date, select MAX(PurchaseDate).

CodePudding user response：

The query you are looking for is as follows.

This uses group by to indicate which columns should be grouped together, and for the column that's not grouped, how to choose which value of many to use, in this case the max value.

Note also the use of explicit, clear, SQL-92 modern join syntax and meaningful column aliases to show which table each column originates from. Distinct is not needed since each group is already unique.

Select c.FirstName, o.Item, Max(o.PurchaseDate) PurchaseDate
from Customers c
join Orders o on o.PersonId=p.PersonId
where c.FirstName = 'Wilbur'
group by c.firstName, o.Item
order by Max(o.PurchaseDate) desc;