groupby summary, with extra details of the summary

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)

groupby summary, with extra details of the summary

Author

Topic

daviesg
Starting Member

1 Post

Posted - 2006-09-13 : 22:36:57

I come across this sort of problem often, can anyone offer a better solution?

“sales” Table:
ProductId
SalesPerson
SalePrice
SaleDate

I wish to know the date of the latest sale of each product, with the price and the saleperson for that sale

My solution is below – is there a better one?

Select I.Product_Id,
I.SalesPerson,
I.SalePrice,
I.SaleDate
from Sales as I
inner join
(SELECT ProductId, max(SaleDate) as MaxDate
from Sales group by ProductId) as M
on M.Productid=I.productid and M.MaxDate=I.SaleDate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-13 : 22:44:38

Alternataive. Try it yourself to see any different in performance


select *
from   sales s
where  SaleDate = (select max(SaleDate) from sales x where x.ProductID = s.ProductID)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-09-14 : 03:35:13

Depends on the data - these queries may not be the same. Performance aside, Khtan's might not work if there's no unique constraint on ProductID/SaleDate because you'd potentially get multiple rows in your scalar subquery. Changing the = to IN would be safer and make it equivilant to the original.
The original proposed query will not crash under those conditions but might not give you the answer you are expecting where there are multiple dates/productIDs.
However, from there you can do the performance analysis to see if there's a difference. If there's no difference & the optimiser comes up with a single plan then it's down to personal preference.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-14 : 03:45:06

The inner join method is prefer over others as it is more flexible and you can have multiple column join. The method i posted is just an alternative and will works for the specific scenario.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources