Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

selecting from tables, avoiding multiple results


Hi,
I have two table, Customers and CustomerOrders.  Customers has columns
like CustID, email, firstName, lastName, etc...where CustID is the
primary key for each Customer.  CustomerOrders has the Order info for
each customer, so if a customer bought 5 times it would have 5
records, CustomerOrders has a primary key called CUORID and a foriegn
key of CustID to try back to the user.
I've got a request to get a list of all Customers and their last order
amount and date of purchase, both amount and purchase date are ni
CustomerOrders.  I can pull the customer data fine from Customers, but
doing a join to CustomerOrders is bring me all their orders, but for
this request i need just the
last one from CustomerOrders.  Is there a way to do this? Or do i have
to use TempTables, building a query to pull all CustomerOrders entries
with just last orders from each customer and then join that tempTables
entries to Customers to get their name, etc...
Or is there a better and easier way?
Thanks.
On 19 Mar, 21:19, "phil2phil" <philtwop@yahoo.com> wrote:

SELECT C.CustId, C.FirstName, C.LastName,
 O.PurchaseDate, O.PurchaseAmount
FROM Customers AS C
OUTER APPLY
(SELECT TOP 1 CustId, PurchaseDate, PurchaseAmount
 FROM CustomerOrders
 WHERE CustId = C.CustId
 ORDER BY PurchaseDate DESC, CustID DESC) AS O;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

-----------------------------------------------Reply-----------------------------------------------

On 3?20?, ??5?19?, "phil2phil" <philtwop@yahoo.com> wrote:

sql2000 :
SELECT C.CustId, C.FirstName, C.LastName,
 O.PurchaseDate, O.PurchaseAmount
FROM Customers AS C
JOIN
(SELECT CustId, MAX(PurchaseDate), PurchaseAmount
 FROM CustomerOrders GROUP BY CustId,PurchaseAmount ) AS O
ON C. CustId  = O.CustId;
Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc