Find higher number of 2 columns
-
I have 2 columns (int). How can I compare the 2 columns and to see which has the higher number
Thanks
-
USE pubs
SELECT CASE WHEN MaxJobID > MaxJobLevel THEN MaxJobID ELSE MaxJobLevel END AS MaxValue
from
(SELECT MAX(job_id) AS MaxJobID
from employee)
AS JobID
cross join
(SELECT MAX(job_lvl) AS MaxJobLevel
from employee)
AS JobLvl
Everett Wilson
ewilson10@yahoo.com -
Worked a treat
Thanks a lot
-
Why a cross join?
USE pubs
SELECT
MAX(MaxSpaltenWert) AS MaxTabellenWert
FROM
(
SELECT max(job_id) AS MaxSpaltenWert FROM employee
UNION ALL
SELECT max(job_lvl) AS MaxSpaltenWert FROM employee
 
orig_tMaxTabellenWert
---------------
246
Note, that this is a safe methods taking NULLs into account. If you can rule out that any value might ever be NULL there are faster methods using CASE possible:
SELECT
MAX(
CASE WHEN job_id >= job_lvl THEN job_id
WHEN job_lvl >= job_id THEN job_lvl
END) AS MaxTabellenWert
FROM
employee
MaxTabellenWert
---------------
246
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank
I am not sure why, in the case of no NULLs, you are using the MAX() function. The following CASE statement does the same:
select case when job_id>=job_lvl then job_id
else job_lvl
end as MaxVal
from employee
-
Well, basically I used the MAX() function, because I read the response from the original questioner to the cross join solution. Before that it wasnt' clear to me, what the narrative should mean. After reading I think he ist after the absolute maximum over this two columns, while your solution returns the max value for each row.
I also like to make a slight correction.
After corresponding with my cyberspace mentor, I got somewhat slashed for my CASE solution and the misplaced MAX.
This is how it should look like with CASE and MAX and NULLs.
SELECT CASE
WHEN MAX(job_id) > MAX(job_lvl) THEN MAX(job_id)
WHEN MAX(job_lvl) > MAX(job_id) THEN MAX(job_lvl)
END
FROM employee
SELECT CASE
WHEN MAX(job_id) >= MAX(job_lvl) THEN MAX(job_id)
WHEN MAX(job_lvl) >= MAX(job_id) THEN MAX(job_lvl)
END
FROM employee
SELECT CASE
WHEN Max1 >= Max2 THEN Max1
WHEN Max2 >= Max1 THEN Max2
END
FROM
(SELECT MAX(job_id) Max1, MAX(job_lvl) Max2
FROM employee) x
Thank you!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply