SQL Server Performance Forum – Threads Archive

Max function

I have some problem about sql Again!!!
I have the table name aa
id Pricecom1 Pricecom2 Pricecom3 Pricecom4
1 12 54 21 33
2 32 12 31 45
3 33 11 12 22
4 54 44 43 13
5 11 6 100 10 I want to find the maximum number of the button of every column. Form this example it should return 100 I tried
sql="select top 1 * from aaa where (select max(com1,com2,com3)as maxvalue) order by id desc" but it doesn’t work

Are you looking for something like this?
SET NOCOUNT ON
CREATE TABLE max_t
(
[id] INT
, col1 INT
, col2 INT
, col3 INT
, col4 INT
)
INSERT INTO max_t(id, col1, col2, col3,col4) values (1,12,54,21,33)
INSERT INTO max_t(id, col1, col2, col3,col4) values (2,32,12,31,45)
INSERT INTO max_t(id, col1, col2, col3,col4) values (3,33,11,12,22)
INSERT INTO max_t(id, col1, col2, col3,col4) values (4,54,44,43,13)
INSERT INTO max_t(id, col1, col2, col3,col4) values (5,11,6,100,10) SELECT CASE
WHEN MAX(col1) >= MAX(col2) AND MAX(col1) >= MAX(col3) AND MAX(col1) >= MAX(col4) THEN MAX(col1)
WHEN MAX(col2) >= MAX(col1) AND MAX(col2) >= MAX(col3) AND MAX(col2) >= MAX(col4) THEN MAX(col2)
WHEN MAX(col3) >= MAX(col1) AND MAX(col3) >= MAX(col2) AND MAX(col3) >= MAX(col4) THEN MAX(col3)
ELSE MAX(Col4)
END
FROM max_t –Or SELECT CASE
WHEN Max1 >= Max2 AND Max1 >= Max3 AND Max1 >= Max4 THEN Max1
WHEN Max2 >= Max1 AND Max2 >= Max3 AND Max2 >= Max4 THEN Max2
WHEN Max3 >= Max1 AND Max3 >= Max2 AND Max3 >= Max4 THEN Max3
ELSE Max4
END
FROM
(SELECT MAX(col1) Max1, MAX(col2) Max2, MAX(col3) Max3, MAX(col4) Max4
FROM max_t) x DROP TABLE max_t
SET NOCOUNT OFF ———–
100
———–
100 ———————–
–Frank
http://www.insidesql.de
———————–

The max function can except only one parameter. Hence, u can achieve the o/p by creating a simple function as follows: CREATE function dbo.fntest(@COM1 integer,@COM2 integer,@COM3 integer)
returns integer
AS
BEGIN
declare @max integer
IF(@COM1 > @COM2)
BEGIN
IF(@COM1 > @COM3)
set @max = @com1
ELSE
set @max = @COM3
END
ELSE
BEGIN
IF(@COM2 > @COM3)
set @max = @COM2
ELSE
set @max = @COM3
END
return @max
END Followed by the select query: select * from test1
where cast(dbo.fntest(com1,com2,com3) as varchar)+ cast(id as varchar) in
(select top 1 cast(dbo.fntest(com1,com2,com3) as varchar) + cast(id as varchar)
from test1
order by id desc)
Regards, Chetan.

No need for a performance killer like a function this time. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

Why not use a derived table to get the MAX values per column, and do the comparison after that: SELECT
CASE WHEN TMP.Max1 > TMP.Max2 AND TMP.Max1 > TMP.Max3 AND TMP.Max1 > TMP.Max4 THEN TMP.Max1
ELSE WHEN TMP.Max2 > TMP.Max1 AND TMP.Max2 > TMP.Max3 AND TMP.Max2 > TMP.Max4 THEN TMP.Max2
ELSE WHEN TMP.Max3 > TMP.Max1 AND TMP.Max3 > TMP.Max2 AND TMP.Max3 > TMP.Max4 THEN TMP.Max3
ELSE WHEN TMP.Max4 > TMP.Max1 AND TMP.Max4 > TMP.Max2 AND TMP.Max4 > TMP.Max3 THEN TMP.Max4
END
FROM
(SELECT Max(Pricecom1) AS Max1, MAX(Pricecom2) AS Max2, MAX(Pricecom3) AS Max3, MAX(Pricecom4) AS Max4
FROM AA) AS TMP

Actually you don’t need to type TMP… times and times again. See my second suggestion above [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

I forgot to tell you all that I use Microsoft Access 2000

With native Access 2000 tables? ———————–
–Frank
http://www.insidesql.de
———————–

What do you mean ? This is my table in Microsoft Access 2000
I have the table name aa
id Pricecom1 Pricecom2 Pricecom3 Pricecom4
1 12 54 21 33
2 32 12 31 45
3 33 11 12 22
4 54 44 43 13
5 11 6 100 10

It might have been that you’re using an adp project in Access that might have been the front-end to an SQL Server database. Well, I can’t help you with Access. But the equivalent to CASE is IIf.
HTH
———————–
–Frank
http://www.insidesql.de
———————–

Hi Frank – sorry, must have overlooked your second suggestion. As usual: me bad. In Access 2000 you also have the option to use a derived table, but the preferred formatting for the statement in Jet-SQL is to put it in square brackets and to finish it off with a period, like this: SELECT x.Field1
FROM [SELECT MAX(MyField) AS Field1 FROM MyTable]. AS x And like Frank said, you’ll have to use the IIf() function in the Jet query.

No big thing, I’d say [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Adriaan, do you know Access? It seems I’ve forgotten quite a few things. I can’t figure out how to solve this with IIf’s. The only solution I could figure out is <pre> SELECT Max(MAX1) AS Overall_Max FROM [SELECT MAX(col1) AS MAX1 FROM max_t UNION SELECT MAX(col2) AS MAX1 FROM max_t UNION SELECT MAX(col3) AS MAX1 FROM max_t UNION SELECT MAX(col4) AS MAX1 FROM max_t p]. AS something; </pre> But let me say, the T-SQL code looks really more elegant! ———————– –Frank http://www.insidesql.de</a> ———————–

Happy to oblige, Frank. T-SQL is so much more elegant than Jet-SQL, especially when you compare CASE with IIf(). And because IIf() is actually calling out to VBA, you had better make sure that it doesn’t have to be evaluated for thousands of rows. But shame on us both that we didn’t spot that ELSE WHEN should be just WHEN. Also, I realized that we need to use >= instead of >, otherwise if two columns have the same maximum then a Null is returned. Here’s the T-SQL query: SELECT
CASE WHEN TMP.Max1 >= TMP.Max2 AND TMP.Max1 >= TMP.Max3 AND TMP.Max1 >= TMP.Max4 THEN TMP.Max1
WHEN TMP.Max2 >= TMP.Max1 AND TMP.Max2 >= TMP.Max3 AND TMP.Max2 >= TMP.Max4 THEN TMP.Max2
WHEN TMP.Max3 >= TMP.Max1 AND TMP.Max3 >= TMP.Max2 AND TMP.Max3 >= TMP.Max4 THEN TMP.Max3
WHEN TMP.Max4 >= TMP.Max1 AND TMP.Max4 >= TMP.Max2 AND TMP.Max4 >= TMP.Max3 THEN TMP.Max4
END
FROM
(SELECT Max(Pricecom1) AS Max1, MAX(Pricecom2) AS Max2, MAX(Pricecom3) AS Max3, MAX(Pricecom4) AS Max4
FROM AA) AS TMP Here’s the Jet-SQL query: SELECT
IIf(TMP.Max1 >= TMP.Max2 AND TMP.Max1 >= TMP.Max3 AND TMP.Max1 >= TMP.Max4, TMP.Max1,
IIf(TMP.Max2 >= TMP.Max1 AND TMP.Max2 >= TMP.Max3 AND TMP.Max2 >= TMP.Max4, TMP.Max2,
IIf(TMP.Max3 >= TMP.Max1 AND TMP.Max3 >= TMP.Max2 AND TMP.Max3 >= TMP.Max4, TMP.Max3,
IIf(TMP.Max4 >= TMP.Max1 AND TMP.Max4 >= TMP.Max2 AND TMP.Max4 >= TMP.Max3, TMP.Max4))))
END
FROM
[SELECT Max(Pricecom1) AS Max1, MAX(Pricecom2) AS Max2, MAX(Pricecom3) AS Max3, MAX(Pricecom4) AS Max4
FROM AA]. AS TMP

Thanks for figuring that out. I know why I prefer T-SQL over Jet SQL! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan But shame on us both that we didn’t spot that ELSE WHEN should be just WHEN. Also, I realized that we need to use >= instead of >, otherwise if two columns have the same maximum then a Null is returned. <hr height="1" noshade id="quote"></blockquote id="quote"> That’s you, not me [<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Thanks for figuring that out. I know why I prefer T-SQL over Jet SQL! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————– <hr height="1" noshade id="quote"></blockquote id="quote"> Ooops Frank, you’re catching me out again.[xx(]

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan Ooops Frank, you’re catching me out again.[xx(] <hr height="1" noshade id="quote"></blockquote id="quote"> Honestly, that’s not my goal!!! I’m here to learn and I only post to learn. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

Just goes to show that you cannot over-analyze queries.[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]

]]>