MAX and CASE statement - 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)

MAX and CASE statement

Author

Topic

NickyJ
Starting Member

46 Posts

Posted - 2007-10-08 : 12:31:21

I have the following

LocationCode UnitTotal
------------ ----------------------------------------
AZ 49.3500000000
CA 415.7394000000
CO 614.9875000000
CT 735.2040000000
IL 884.6725000000
MA 170.5400000000
NJ 376.4200000000
NY 252.9821000000
WA 3154.0600000000

I want a 3rd column and this to be 1 for the maximum value for Location so as below

LocationCode UnitTotal Wedge
------------ ----------------------------------------
AZ 49.3500000000 0
CA 415.7394000000 0
CO 614.9875000000 0
CT 735.2040000000 0
IL 884.6725000000 0
MA 170.5400000000 0
NJ 376.4200000000 0
NY 252.9821000000 0
WA 3154.0600000000 1

Can I do this via case statement ? Thanks all

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-08 : 12:34:01

select LocationCode, UnitTotal, case when LocationCode = (select max(LocationCode) from tbl) then 1 else 0 end
from tbl

select LocationCode, UnitTotal, case when LocationCode = maxloc.LocationCode then 1 else 0 end
from tbl
cross join (select max(LocationCode) from tbl) maxloc

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-10-08 : 12:34:28

Try this:

SELECT [LocationCode], [UnitTotal], CASE WHEN [UnitTotal] = (SELECT MAX(UnitTotal) FROM YourTable) THEN 1 ELSE 0 END AS [Wedge]
FROM YourTable

SQL Server Helper
http://www.sql-server-helper.com

Kristen
Test

22859 Posts

Posted - 2007-10-08 : 12:37:02

Will that cache, or re-calculate for each row?


DECLARE @TestData TABLE
(
	LocationCode	varchar(2),
	UnitTotal	float
)

INSERT INTO @TestData
SELECT 'AZ', 49.3500000000 UNION ALL
SELECT 'CA', 415.7394000000 UNION ALL
SELECT 'CO', 614.9875000000 UNION ALL
SELECT 'CT', 735.2040000000 UNION ALL
SELECT 'IL', 884.6725000000 UNION ALL
SELECT 'MA', 170.5400000000 UNION ALL
SELECT 'NJ', 376.4200000000 UNION ALL
SELECT 'NY', 252.9821000000 UNION ALL
SELECT 'WA', 3154.0600000000

SELECT	T1.LocationCode, T1.UnitTotal,
	[Top?] = CASE WHEN T1.UnitTotal = T2.MAX_UnitTotal THEN 1 ELSE 0 END
FROM	@TestData AS T1,
	(
		SELECT	[MAX_UnitTotal] = MAX(UnitTotal)
		FROM	@TestData
	) AS T2

Kristen

NickyJ
Starting Member

46 Posts

Posted - 2007-10-09 : 03:53:54

Thanks for solutions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources