Retrieving just the first instance

[MeanJoeGreen13]

I use the following code to pull a list of customers who have placed an order for a particular product between two dates. However, if the customer has ordered this item more than once in the date period multiple records appear for the customer. How can I change the SQL to just show me one record per customer?

The SQL so far is:

SELECT Customers.Title, Customers.[First Name], Customers.[Last Name], Customers.Address1, Customers.Address2, Customers.Town, Customers.County, Customers.Postcode, Customers.Country, Sales.[Sales Date], Sales.[Sales Date], SalesD.[Item Code]
FROM Customers INNER JOIN (Sales INNER JOIN SalesD ON Sales.SalesID = SalesD.SalesID) ON Customers.ID = Sales.CustomerID
WHERE (((Sales.[Sales Date])>=#10/1/2007# And (Sales.[Sales Date])<=#10/31/2007#) AND ((SalesD.[Item Code])="BA0002"));

 

[ca8msm]

You are returning the "Sales Date" as part of your query, so if they've made to orders which one do you want to see? Either way, you'll need to use the Group By and Min or Max functions so look up how to use them in your help files.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

>=#10/1/2007#

this won't fly in SQL server
you need

>='20071001'

SQL Server is not access you know!

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions