for each cust, show highest sale

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 2005 Forums

Transact-SQL (2005)

for each cust, show highest sale

Author

Topic

Vanthu
Starting Member

2 Posts

Posted - 2008-04-01 : 18:34:14

I haven't done any SQL in a while. Having trouble getting me head around this one.

I have a table Sales:
(Transaction_ID int, Customer_ID, Sales_DateTime Time, Sales_Amt Dollar)

I need a query that returns:
For each Customer_ID that has made a purchase
Show the highest dollar Sales_Amt they have ever made

In C++ I would probably do a recursive solution, is this doable? advisable? in T-SQL?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 18:43:16

Recursion is not advisable in SQL for performance reasons. It is faster to do things in sets.

SELECT Customer_ID, MAX(Sales_Amt) AS HighestSale
FROM Sales
GROUP BY Customer_ID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-01 : 18:44:01

[code]SELECT
Cusomer_ID
MAX(Sales_Amt) AS MaxSalesAmount
FROM
Sales
GROUP BY
Customer_ID[/code]

Damn! :)

Vanthu
Starting Member

2 Posts

Posted - 2008-04-01 : 18:56:19

yes, that is exactly what I needed. Thank you.

I spent all day on that, and it was so simple.

I am rusty, and humbled.

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources