Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2014
    Posts
    9

    Angry Unanswered: Max value from multiple columns

    OK, so I have a table similar to the below:

    ID Date t1 t2 t3
    1 01/01/2016 6 8 9
    1 02/01/2016 8 4 2
    1 03/01/2016 7 5 12
    2 01/01/2016 9 21 64
    2 02/01/2016 12 14 1
    2 03/01/2016 16 17 6
    3 01/01/2016 6 6 9
    3 02/01/2016 21 26 14
    3 03/01/2016 9 4 2

    I'd like to bring back the following:

    ID Date Max t
    1 01/01/2016 9
    1 02/01/2016 8
    1 03/01/2016 12
    2 01/01/2016 64
    2 02/01/2016 14
    2 03/01/2016 17
    3 01/01/2016 9
    3 02/01/2016 26
    3 03/01/2016 9

    Is there anyway I can do this?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Here's a SQL Fiddle http://sqlfiddle.com/#!9/1d832/2 that offers one solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2014
    Posts
    9
    Quote Originally Posted by Pat Phelan View Post
    Here's a SQL Fiddle http://sqlfiddle.com/#!9/1d832/2 that offers one solution.

    -PatP
    Thanks Pat,

    I've clicked it, but there is no code showing? :\

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try loading from a normal webpage. suspect SQLfiddle has issues with tablet or phone web clients

    ..but Pat's code is as follows
    the test data/schema
    Code:
    CREATE TABLE foo
    (  ID   INT   NOT NULL
    ,  Date DATE  NOT NULL
    ,  t1   INT   NOT NULL
    ,  t2   INT   NOT NULL
    ,  t3   INT   NOT NULL
    );
    
    INSERT INTO foo (ID, Date, t1, t2, t3) VALUES
       (1, '2016-01-01',  6,  8,  9),
       (1, '2016-02-01',  8,  4,  2),
       (1, '2016-03-01',  7,  5, 12),
       (2, '2016-01-01',  9, 21, 64),
       (2, '2016-02-01', 12, 14,  1),
       (2, '2016-03-01', 16, 17,  6),
       (3, '2016-01-01',  6,  6,  9),
       (3, '2016-02-01', 21, 26, 14),
       (3, '2016-03-01',  9,  4,  2);
    the query
    Code:
    SELECT a.ID, a.Date, Max(a.t) AS 'Max t'
       FROM (
          SELECT v1.ID, v1.Date, v1.t1 AS t FROM foo AS v1 UNION
          SELECT v2.ID, v2.Date, v2.t2 AS t FROM foo AS v2 UNION
          SELECT v3.ID, v3.Date, v3.t3 AS t FROM foo AS v3) AS a
       GROUP BY a.ID, a.Date
       ORDER BY a.ID, a.Date;
    the output
    Code:
    	Date	Max t
    1	January, 01 2016 00:00:00	9
    1	February, 01 2016 00:00:00	8
    1	March, 01 2016 00:00:00	12
    2	January, 01 2016 00:00:00	64
    2	February, 01 2016 00:00:00	14
    2	March, 01 2016 00:00:00	17
    3	January, 01 2016 00:00:00	9
    3	February, 01 2016 00:00:00	26
    3	March, 01 2016 00:00:00	9
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by thoron6 View Post
    I've clicked it, but there is no code showing? :\
    SQL Fiddle takes about 30 seconds to render the page, it doesn't load instantaneously because SQL Fiddle serves a lot of clients (hundreds to thousands of clients at any given minute) and has to load and execute both the schema code and the query code before it can render the page. Try to give it a minute or so, and see if it loads then.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    It wouldn't load for me from an Android tablet or phone.....
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2014
    Posts
    9
    Quote Originally Posted by Pat Phelan View Post
    SQL Fiddle takes about 30 seconds to render the page, it doesn't load instantaneously because SQL Fiddle serves a lot of clients (hundreds to thousands of clients at any given minute) and has to load and execute both the schema code and the query code before it can render the page. Try to give it a minute or so, and see if it loads then.

    -PatP
    Thanks Pat,

    This worked great... I now have the max number per day....

    Is it possible to group the results per month to get the largest per month?

    Thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so why not try and modify the query to your (new) requirements

    you already have a test environment, arguably with too small a test data set but at least make an effort
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •