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
lynntonAsked:
Who is Participating?
 
boblahConnect With a Mentor 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
 
LowfatspreadConnect With a Mentor 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
 
boblahCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
boblahCommented:
Lowfatspread,

Nice! very cute.

Not sure about performance, tho'.
0
 
lynntonAuthor Commented:
Lowfatspread,

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

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

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

Thanks,
Lynnton
0
 
lynntonAuthor Commented:
boblah,

Same error message with your awsome query friend.

Thanks,
Lynnton

0
 
boblahCommented:
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
 
lynntonAuthor Commented:
boblah,

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

Thanks,
Lynnton
0
 
lynntonAuthor 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
 
lynntonAuthor 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
All Courses

From novice to tech pro — start learning today.