Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2004
    Posts
    11

    Unanswered: Find highest value of three fields for each record

    I am trying to create a query that will find the highest value of three fields.
    Each field is a number and I need an the equation to determine the highest of the three fields for each record. I am not sure what the formula would look like.

    I appreciate any help.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    What are you trying to get
    a. the record with the highest combined value for the 3 fields ie
    1 2 3 = 6
    4 3 1 = 8
    6 4 1 = 11 this is the one you want

    b. the records that have the highest in each field
    1 2 3 = this one has the highest third value
    4 3 1 = not returned
    6 4 1 = this has the highest first 2 values

    c. the highest values in the fields
    1 2 3
    4 3 1
    6 4 1

    the return would be
    = 6 4 3
    Last edited by m.timoney; 01-26-07 at 05:51.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    highest of the three fields for each record
    1 2 3 = 3
    4 3 1 = 4
    6 4 1 = 6
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT TOP 1
    ...
    ORDER BY [fieldname]
    Something like that?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, there are three fields (see post #1), not one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ohh of course - my bad.

    so it's like this?

    +------+------+------+
    | field1 | field2 | field3 |
    +------+------+------+
    |__1___|__2__|__3___|
    |__4___|__3__|__1___|

    |__6___|__4__|__1___|
    +------+------+------+
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i see you are beginning to catch up, george
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    ah misread the post that does make more sense

    there's the good old
    select *,
    iif(field2>field3,field2,field3) as tmpField,
    iif(field1>tmpField,field1,tmpField) as Max
    From YourTable;
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    i see you are beginning to catch up, george
    Haha, thanks.

    Would this work then?
    Code:
    (CASE WHEN [Field1] > [Field2] AND [Field1] > [Field3] THEN [Field1] ELSE
    (CASE WHEN [Field2] > [Field1] AND [Field2] > [Field3] THEN [Field2] ELSE
    (CASE WHEN [Field3] > [Field1] AND [Field3] > [Field2] THEN [Field3] ELSE 'Unknown'
    	END)
    	END)
    	END)
    AS 'Highest Value'
    
    FROM [YourTable]
    Not tried a case with an and before...
    Is that possible?

    Anyway, I hope that works!

    -GeorgeV
    Last edited by gvee; 01-26-07 at 09:26.
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, you are definitely on the right track

    two things:

    first, you can eliminate the ELSE 'Unknown' as well as the third WHEN simply by using greater-than-or-equal instead of greater-than, and of course you don't need to nest the CASE expressions, just stack the WHEN clauses
    Code:
    select case when field1 >= field2 
                 and field1 >= field3
                then field1
                when field2 >= field1 
                 and field2 >= field3
                then field2
                else field3 end       as max_field
    the second thing i want to point out is that ms access uses IIF, not CASE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    george, you are definitely on the right track
    That's all I wanted to hear
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, so here's my solution:
    Code:
    SELECT Table1.KeyField,
    IIf([field1]>=[field2] And [field1]>=[field3],[field1],
    IIf([field2]>=[field1] And [field1]>=[field3],[field2],
    IIf([field3]>=[field2] And [field1]>=[field1],[field3],
    ))) AS Highest
    FROM Table1
    Which I have tested and it works.

    *waits for r937 to correct coding and neaten it up*
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, great progress

    you couldn't possibly have tested it, because you have a dangling comma

    also, the third IIF is unnecessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    yes however i perfer to assign the value to a tmp means you only have to change 1 iif equation if the criteria changes, cant you tell i'm a coder first and a DBA second
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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