lynnton
asked on
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),s um(wednesd ay),sum(th ursday), sum(friday),sum(saturday), sum(sunday )
) from required
Please only standard ansi sql query.
Thanks,
Lynnton
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),s
) from required
Please only standard ansi sql query.
Thanks,
Lynnton
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lowfatspread,
Nice! very cute.
Not sure about performance, tho'.
Nice! very cute.
Not sure about performance, tho'.
ASKER
Lowfatspread,
error message on visual basic.
"Method of 'open' of 'Object _Recordset ' failed
Thanks,
Lynnton
error message on visual basic.
"Method of 'open' of 'Object _Recordset ' failed
Thanks,
Lynnton
Use query analyser to make sure the query works, get more detailed error messages if not, and check it's performance etc.
ASKER
Lowfatspread,
By the way i'm using ms access as database.
Thanks,
Lynnton
By the way i'm using ms access as database.
Thanks,
Lynnton
ASKER
boblah,
Same error message with your awsome query friend.
Thanks,
Lynnton
Same error message with your awsome query friend.
Thanks,
Lynnton
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(thursda y) AND
sum(wednesday)>sum(friday) AND
sum(wednesday)>sum(saturda y) AND
sum(wednesday)>sum(sunday)
, sum(wednesday),
sum(thursday)>sum(monday) AND
sum(thursday)>sum(tuesday) AND
sum(thursday)>sum(wednesda y) 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(wednesda y) 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
SELECT
SWITCH(
sum(monday)>sum(tuesday) AND
sum(monday)>sum(wednesday)
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
sum(tuesday)>sum(thursday)
sum(tuesday)>sum(friday) AND
sum(tuesday)>sum(saturday)
sum(tuesday)>sum(sunday)
, sum(tuesday),
sum(wednesday)>sum(monday)
sum(wednesday)>sum(tuesday
sum(wednesday)>sum(thursda
sum(wednesday)>sum(friday)
sum(wednesday)>sum(saturda
sum(wednesday)>sum(sunday)
, sum(wednesday),
sum(thursday)>sum(monday) AND
sum(thursday)>sum(tuesday)
sum(thursday)>sum(wednesda
sum(thursday)>sum(friday) AND
sum(thursday)>sum(saturday
sum(thursday)>sum(sunday)
, sum(thursday),
sum(friday)>sum(monday) AND
sum(friday)>sum(tuesday) AND
sum(friday)>sum(wednesday)
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)
sum(saturday)>sum(wednesda
sum(saturday)>sum(thursday
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
ASKER
boblah,
It works, no errors, also no results. (just a single column with no data)
Thanks,
Lynnton
It works, no errors, also no results. (just a single column with no data)
Thanks,
Lynnton
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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"
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"
Can' t think of a clever way, so:
SELECT
CASE
WHEN
sum(monday)>sum(tuesday) AND
sum(monday)>sum(wednesday)
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
sum(tuesday)>sum(thursday)
sum(tuesday)>sum(friday) AND
sum(tuesday)>sum(saturday)
sum(tuesday)>sum(sunday)
THEN sum(tuesday)
WHEN
sum(wednesday)>sum(monday)
sum(wednesday)>sum(tuesday
sum(wednesday)>sum(thursda
sum(wednesday)>sum(friday)
sum(wednesday)>sum(saturda
sum(wednesday)>sum(sunday)
THEN sum(wednesday)
WHEN
sum(thursday)>sum(monday) AND
sum(thursday)>sum(tuesday)
sum(thursday)>sum(wednesda
sum(thursday)>sum(friday) AND
sum(thursday)>sum(saturday
sum(thursday)>sum(sunday)
THEN sum(thursday)
WHEN
sum(friday)>sum(monday) AND
sum(friday)>sum(tuesday) AND
sum(friday)>sum(wednesday)
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)
sum(saturday)>sum(wednesda
sum(saturday)>sum(thursday
sum(saturday)>sum(friday) AND
sum(saturday)>sum(sunday)
THEN sum(saturday)
ELSE
sum(sunday)
END
FROM required
Cheers!