Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

get column contains highest value

Posted on 2004-10-29
13
Medium Priority
?
349 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:lynnton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
13 Comments
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 800 total points
ID: 12443352
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
 
LVL 6

Expert Comment

by:boblah
ID: 12443371
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
 
LVL 6

Expert Comment

by:boblah
ID: 12443410
Lowfatspread,

Nice! very cute.

Not sure about performance, tho'.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:lynnton
ID: 12443543
Lowfatspread,

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

Thanks,
Lynnton
0
 
LVL 6

Expert Comment

by:boblah
ID: 12443567
Use query analyser to make sure the query works, get more detailed error messages if not, and check it's performance etc.
0
 
LVL 1

Author Comment

by:lynnton
ID: 12443662
Lowfatspread,

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

Thanks,
Lynnton
0
 
LVL 1

Author Comment

by:lynnton
ID: 12443917
boblah,

Same error message with your awsome query friend.

Thanks,
Lynnton

0
 
LVL 6

Expert Comment

by:boblah
ID: 12444134
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
 
LVL 1

Author Comment

by:lynnton
ID: 12444206
boblah,

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

Thanks,
Lynnton
0
 
LVL 6

Accepted Solution

by:
boblah earned 1200 total points
ID: 12444579
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
 
LVL 1

Author Comment

by:lynnton
ID: 12452345
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
 
LVL 1

Author Comment

by:lynnton
ID: 12461035
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question