Sum of max - 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)

Sum of max

Author

Topic

raja
Starting Member

18 Posts

Posted - 2005-03-02 : 01:24:06

Hi

i've a table

Project Drawing Revision Weight
A001 001 0 10
A001 001 1 11
A001 001 2 12
A001 001 2 13
A001 001 2 12
A001 001 3 33
A001 001 4 22
A001 001 4 21
A001 001 4 22

from the above table i want to get the sum of the Weight for the maximum revsion number which should be grouped by project and drawing.
so the result would be:
A001 001 4 65

and i want to do this in a single select statement and i don't want to have a subquery to do this.Can this be done ?

Thanks In Advance
Raja

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-02 : 04:01:02

select top 1 /*with ties*/
project,drawing,revision,sum(weight)
group by project,drawing,revision
order by sum(weight) desc

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-02 : 13:47:10

Why is it a requirement to not have a subquery?

quote:
Originally posted by raja

... and i don't want to have a subquery to do this...

Thanks In Advance
Raja

Codo Ergo Sum

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-02 : 15:05:22

Rockmoose's works if the project and drawing doesn't change for the whole table


--Your table ----------------------------------------------------
set nocount on
declare @tb Table (Project char(4), Drawing char(3), Revision int, Weight int)
insert @tb 
Select 'A001', '001', 0, 10 union all
Select 'A001', '001', 1, 11 union all
Select 'A001', '001', 2, 12 union all
Select 'A001', '001', 2, 13 union all
Select 'A001', '001', 2, 12 union all
Select 'A001', '001', 3, 33 union all
Select 'A001', '001', 4, 22 union all
Select 'A001', '001', 4, 21 union all
Select 'A001', '001', 4, 22
--Your table ----------------------------------------------------

declare @tb2 Table (Project char(4), Drawing char(3), Revision int)

insert @tb2
Select	Project
	,Drawing
	,max(Revision) revision
From	@tb
Group by 
	Project
	,Drawing



Select	b.Project
	,b.Drawing
	,b.revision
	,sum(b.weight) [sumweight]
From	@tb2 a
JOIN	@tb b 
		ON a.Project = b.Project
		and a.Drawing = b.Drawing
		and a.revision = b.revision
Group by
	b.Project
	,b.Drawing
	,b.revision

-----------------------------------------
--With derived table (unless you consider this a subquery)
Select	b.Project
	,b.Drawing
	,b.revision
	,sum(b.weight) [sumweight]
From	(
	Select	Project
		,Drawing
		,max(Revision) revision
	From	@tb
	Group by 
		Project
		,Drawing
	) as  a
JOIN	@tb b 
		ON a.Project = b.Project
		and a.Drawing = b.Drawing
		and a.revision = b.revision
Group by
	b.Project
	,b.Drawing
	,b.revision

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-02 : 15:39:17

Yup, my bad.
TG's derived table would be the way to go,
very clean and elegant solution.

If You are working a lot with the "last" revision,
it might make sense to define a view to return only the records of the latest version.

rockmoose

raja
Starting Member

18 Posts

Posted - 2005-03-03 : 05:02:01

thank you guyz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources