# get column contains highest value

Hi,

1. How to get the column which contains the highest value?
table with column as monday, tuesday, wednesday, thursday, friday, saturday, sunday

here's where i'm at now,
SELECT max(*) FROM (
select sum(monday),sum(tuesday),sum(wednesday),sum(thursday), sum(friday),sum(saturday),sum(sunday)
)  from required

Please only standard ansi sql query.

Thanks,
Lynnton
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
SELECT max(x) FROM (
select u,sum(case when u=1 then monday
when u=2 then tuesday
when u=3 then wednesday
when u=4 then thursday
when u=5 then friday
when u=6 then saturday
else sunday
end) as x
from required
,(select 1 as U union select 2 as u union select 3 as u union select 4 as u
union select 5 as u union select 6 as u union select 7 as u ) as x
group by U
) as Z

0
Commented:
Hi lynnton,

Can' t think of a clever way, so:

SELECT
CASE
WHEN
sum(monday)>sum(tuesday) AND
sum(monday)>sum(wednesday) AND
sum(monday)>sum(thursday) AND
sum(monday)>sum(friday) AND
sum(monday)>sum(saturday) AND
sum(monday)>sum(sunday)
THEN  sum(monday)
WHEN
sum(tuesday)>sum(monday) AND
sum(tuesday)>sum(wednesday) AND
sum(tuesday)>sum(thursday) AND
sum(tuesday)>sum(friday) AND
sum(tuesday)>sum(saturday) AND
sum(tuesday)>sum(sunday)
THEN  sum(tuesday)
WHEN
sum(wednesday)>sum(monday) AND
sum(wednesday)>sum(tuesday) AND
sum(wednesday)>sum(thursday) AND
sum(wednesday)>sum(friday) AND
sum(wednesday)>sum(saturday) AND
sum(wednesday)>sum(sunday)
THEN  sum(wednesday)
WHEN
sum(thursday)>sum(monday) AND
sum(thursday)>sum(tuesday) AND
sum(thursday)>sum(wednesday) AND
sum(thursday)>sum(friday) AND
sum(thursday)>sum(saturday) AND
sum(thursday)>sum(sunday)
THEN  sum(thursday)
WHEN
sum(friday)>sum(monday) AND
sum(friday)>sum(tuesday) AND
sum(friday)>sum(wednesday) AND
sum(friday)>sum(thursday) AND
sum(friday)>sum(saturday) AND
sum(friday)>sum(sunday)
THEN  sum(friday)
WHEN
sum(saturday)>sum(monday) AND
sum(saturday)>sum(tuesday) AND
sum(saturday)>sum(wednesday) AND
sum(saturday)>sum(thursday) AND
sum(saturday)>sum(friday) AND
sum(saturday)>sum(sunday)
THEN  sum(saturday)
ELSE
sum(sunday)
END
FROM required

Cheers!
0
Commented:

Nice! very cute.

0
Author Commented:

error message on visual basic.
"Method of 'open' of 'Object _Recordset ' failed

Thanks,
Lynnton
0
Commented:
Use query analyser to make sure the query works, get more detailed error messages if not, and check it's performance etc.
0
Author Commented:

By the way i'm using ms access as database.

Thanks,
Lynnton
0
Author Commented:
boblah,

Same error message with your awsome query friend.

Thanks,
Lynnton

0
Commented:
Ok, access, that makes a difference, try:

SELECT
SWITCH(
sum(monday)>sum(tuesday) AND
sum(monday)>sum(wednesday) AND
sum(monday)>sum(thursday) AND
sum(monday)>sum(friday) AND
sum(monday)>sum(saturday) AND
sum(monday)>sum(sunday)
,  sum(monday),

sum(tuesday)>sum(monday) AND
sum(tuesday)>sum(wednesday) AND
sum(tuesday)>sum(thursday) AND
sum(tuesday)>sum(friday) AND
sum(tuesday)>sum(saturday) AND
sum(tuesday)>sum(sunday)
,  sum(tuesday),

sum(wednesday)>sum(monday) AND
sum(wednesday)>sum(tuesday) AND
sum(wednesday)>sum(thursday) AND
sum(wednesday)>sum(friday) AND
sum(wednesday)>sum(saturday) AND
sum(wednesday)>sum(sunday)
,  sum(wednesday),

sum(thursday)>sum(monday) AND
sum(thursday)>sum(tuesday) AND
sum(thursday)>sum(wednesday) AND
sum(thursday)>sum(friday) AND
sum(thursday)>sum(saturday) AND
sum(thursday)>sum(sunday)
,  sum(thursday),

sum(friday)>sum(monday) AND
sum(friday)>sum(tuesday) AND
sum(friday)>sum(wednesday) AND
sum(friday)>sum(thursday) AND
sum(friday)>sum(saturday) AND
sum(friday)>sum(sunday)
,  sum(friday),

sum(saturday)>sum(monday) AND
sum(saturday)>sum(tuesday) AND
sum(saturday)>sum(wednesday) AND
sum(saturday)>sum(thursday) AND
sum(saturday)>sum(friday) AND
sum(saturday)>sum(sunday)
,  sum(saturday),

true,
sum(sunday)
)
FROM required

Also, if that doesn't work, then try it in the access front end first to shake it down, or see if the problem is with the query or the ADO code
0
Author Commented:
boblah,

It works, no errors, also no results. (just a single column with no data)

Thanks,
Lynnton
0
Commented:
what happens with:

SELECT
(sum(monday)>sum(tuesday) AND
sum(monday)>sum(wednesday) AND
sum(monday)>sum(thursday) AND
sum(monday)>sum(friday) AND
sum(monday)>sum(saturday) AND
sum(monday)>sum(sunday)) AS mondayYes,

sum(monday) as mon,
sum(tuesday) as tue,
sum(wednesday) as wed,
sum(thursday) as thu,
sum(friday) as fri,
sum(saturday) as sat,
sum(sunday) AS sun,

SWITCH(
sum(monday)>sum(tuesday) AND
sum(monday)>sum(wednesday) AND
sum(monday)>sum(thursday) AND
sum(monday)>sum(friday) AND
sum(monday)>sum(saturday) AND
sum(monday)>sum(sunday)
,  sum(monday),

sum(tuesday)>sum(monday) AND
sum(tuesday)>sum(wednesday) AND
sum(tuesday)>sum(thursday) AND
sum(tuesday)>sum(friday) AND
sum(tuesday)>sum(saturday) AND
sum(tuesday)>sum(sunday)
,  sum(tuesday),

sum(wednesday)>sum(monday) AND
sum(wednesday)>sum(tuesday) AND
sum(wednesday)>sum(thursday) AND
sum(wednesday)>sum(friday) AND
sum(wednesday)>sum(saturday) AND
sum(wednesday)>sum(sunday)
,  sum(wednesday),

sum(thursday)>sum(monday) AND
sum(thursday)>sum(tuesday) AND
sum(thursday)>sum(wednesday) AND
sum(thursday)>sum(friday) AND
sum(thursday)>sum(saturday) AND
sum(thursday)>sum(sunday)
,  sum(thursday),

sum(friday)>sum(monday) AND
sum(friday)>sum(tuesday) AND
sum(friday)>sum(wednesday) AND
sum(friday)>sum(thursday) AND
sum(friday)>sum(saturday) AND
sum(friday)>sum(sunday)
,  sum(friday),

sum(saturday)>sum(monday) AND
sum(saturday)>sum(tuesday) AND
sum(saturday)>sum(wednesday) AND
sum(saturday)>sum(thursday) AND
sum(saturday)>sum(friday) AND
sum(saturday)>sum(sunday)
,  sum(saturday),

true,
sum(sunday)
)
FROM required
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
boblah,

Sad to say samething i think this query will help us..

SQL = "SELECT TOP 1 * FROM " & _
"( " & _
"SELECT Monday as DayName, SUM(Monday) As DaySum FROM required " & _
"Union All " & _
"SELECT Tuesday, SUM(Tuesday) FROM required " & _
"Union All " & _
"SELECT Wednesday, SUM(Wednesday) FROM required " & _
"Union All " & _
"SELECT Thursday, SUM(Thursday) FROM required " & _
"Union All " & _
"SELECT Friday, SUM(Friday) FROM required " & _
"Union All " & _
"SELECT Saturday, SUM(Saturday) FROM required " & _
"Union All " & _
"SELECT Sunday, SUM(Sunday) FROM required " & _
") As B ORDER BY Daysum"

but it returns an error message "you tried to execute a query that does not include the specified expression
'DyaName' as part of the aggregate function."

Thanks,
Lynnton
0
Author Commented:
got it !!!

here's the working version.

SQL = "SELECT  top 1 * FROM " & _
"( " & _
"SELECT 'Monday' as DayName, SUM(Monday) As DaySum FROM required " & _
"Union All " & _
"SELECT 'Tuesday', SUM(Tuesday) FROM required " & _
"Union All " & _
"SELECT 'Wednesday', SUM(Wednesday) FROM required " & _
"Union All " & _
"SELECT 'Thursday', SUM(Thursday) FROM required " & _
"Union All " & _
"SELECT 'Friday', SUM(Friday) FROM required " & _
"Union All " & _
"SELECT 'Saturday', SUM(Saturday) FROM required " & _
"Union All " & _
"SELECT 'Sunday', SUM(Sunday) FROM required " & _
" As B) ORDER BY Daysum desc"
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.