Group by clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group by clause

Author

Topic

martalex
Starting Member

17 Posts

Posted - 2004-12-16 : 22:49:29

I have the following tables. How can I return a list of all customers and their most recent order?

Customer
--------
customer_id
Name

Order
----
Order_id
Customer_id
Date
description
Amount

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-16 : 23:33:18

select * from BooksOnline

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

B0g
Starting Member

19 Posts

Posted - 2004-12-17 : 02:03:06

Try this...

SELECT customer_id,
Name,
(SELECT TOP 1 order_id FROM Order O WHERE O.Customer_id = C.Customer_id ORDER BY Date DESC) AS order_id,
(SELECT TOP 1 Date FROM Order O WHERE O.Customer_id = C.Customer_id ORDER BY Date DESC) AS Date,
(SELECT TOP 1 Description FROM Order O WHERE O.Customer_id = C.Customer_id ORDER BY Date DESC) AS Description,
(SELECT TOP 1 Amount FROM Order O WHERE O.Customer_id = C.Customer_id ORDER BY Date DESC) AS Amount,
FROM Customer C

Hippi
Yak Posting Veteran

63 Posts

Posted - 2004-12-17 : 14:16:13

quote:
Originally posted by martalex

I have the following tables. How can I return a list of all customers and their most recent order?

Customer
--------
customer_id
Name

Order
----
Order_id
Customer_id
Date
description
Amount

How about
select c.name, o.order_id, o.Date, o.description, o.amount from orders o, Customer c
where date=(select max(date)from order od1 where o.Name=od1.Name)
and o.id=C.id

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-17 : 16:37:30

select c.Name, Max(o.Date)
from Customer c
join Order o on o.Customer_ID = c.Customer_ID
group by c.Name

HTH

=================================================================

Happy Holidays!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources