1. Registered User
Join Date
Feb 2002
Posts
1

Hi all. I'm looking for assistance to get the greatest value from 3 or more different columns. I'm assuming that the best way is to put the columns into a temp table and use 'max' function to return the greatest value but don't know how to code it. Thanks in advance.

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Code:
```CREATE TABLE #minnest (
a		INT
,  b		INT
,  c		INT
)

INSERT INTO #minnest (a, b, c)
SELECT  1,  2,  3 UNION
SELECT 11, 22, 33 UNION
SELECT 21, 22, 23 UNION
SELECT 31, 32, 33 UNION
SELECT 41, 42, 43

SELECT
CASE
WHEN Min(a) < Min(b) AND Min(a) < Min(c) THEN Min(a)
WHEN Min(b) < Min(c) THEN Min(b)
ELSE Min(c)
END
FROM #minnest

DROP TABLE #minnest```
-PatP

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
I've always had a preference for formula-based solutions over boolena logic:
Code:
```create function dbo.Biggest(@Val1 decimal(38, 10), @Val2 decimal(38, 10))
returns decimal(38, 10)
as
--Function Biggest
--Returns the larger of two numbers
--
--blindman, 3/15/2006
begin
return (@Val1 + @Val2 + abs(@Val1 - @Val2))/2
end```
Usage:
Code:
```CREATE TABLE #minnest (
a		INT
,  b		INT
,  c		INT
)

INSERT INTO #minnest (a, b, c)
SELECT  1,  2,  3 UNION
SELECT 11, 22, 33 UNION
SELECT 21, 22, 23 UNION
SELECT 31, 32, 33 UNION
SELECT 41, 42, 43

SELECT dbo.Biggest(a, dbo.Biggest(b, c)) FROM #minnest

DROP TABLE #minnest```

4. Registered User
Join Date
Jan 2006
Location
Singapore
Posts
47
Alternate method
Code:
```create function dbo.Biggest
(
@val1 decimal(38,10),
@val2 decimal(38,10),
@val3 decimal(38,10)
)
returns decimal(38,10)
as
begin
return
(
select max(val)
from
(
select @val1 as val union all
select @val2 as val union all
select @val3 as val
) as m
)
end
go
select dbo.Biggest(2, 4, 8)```

5. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
instead of putting your data into a temp table, put it into mysql where you can use the built-in GREATEST function

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Quit pimpin' yo' lo-class engines on our street corner, Jack!

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by blindman
Quit pimpin' yo' lo-class engines on our street corner, Jack!
<voice type="bender">everyplace else it's "sql server can do this" and "sql server can do that" but do you guys like it when someone comes in here and shows you something sql server should be able to do but can't?noooooooooo-o-o-o-o</voice>

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by r937
instead of putting your data into a temp table, put it into mysql where you can use the built-in GREATEST function
There are actually a lot of languages that implement iterative functionality like the Greatest function. I think that Access, Perl, PHP, etc all either implement it directly (as delivered) or can easily create a function like Blindman did to add that kind of functionality.

Min and Max aren't truly set based functions, but they are usually implemented in the database engine itself instead of in the DRL script interpreter. That makes Min and Max much more efficient than an interpreted function added after the row fetch has been completed such as Greatest.

-PatP

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•