Solved

get column contains highest value

Posted on 2004-10-29
343 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
Question by:lynnton
    12 Comments
     
    LVL 50

    Assisted Solution

    by:Lowfatspread
    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
    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
    Lowfatspread,

    Nice! very cute.

    Not sure about performance, tho'.
    0
     
    LVL 1

    Author Comment

    by:lynnton
    Lowfatspread,

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

    Thanks,
    Lynnton
    0
     
    LVL 6

    Expert Comment

    by:boblah
    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
    Lowfatspread,

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

    Thanks,
    Lynnton
    0
     
    LVL 1

    Author Comment

    by:lynnton
    boblah,

    Same error message with your awsome query friend.

    Thanks,
    Lynnton

    0
     
    LVL 6

    Expert Comment

    by:boblah
    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
    boblah,

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

    Thanks,
    Lynnton
    0
     
    LVL 6

    Accepted Solution

    by:
    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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Suggested Solutions

    Title # Comments Views Activity
    Query Help 3 29
    Using a join in dynamic sql 3 19
    sql server/vb.net 15 16
    SQL Select - Query to Summarize Records 3 15
    The steps for moving the system databases to a new location are documented in the following technical article: http://msdn.microsoft.com/en-us/library/ms345408(v=SQL.90).aspx. However sometimes after the moving process is finished, though SQL i…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    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.
    Viewers will learn how the fundamental information of how to create a table.

    913 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now