# Thread: Find highest value of three fields for each record

1. Registered User
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. Registered User
Join Date
May 2005
Location
Posts
2,888

3. Registered User
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.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
highest of the three fields for each record
1 2 3 = 3
4 3 1 = 4
6 4 1 = 6

5. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Code:
```SELECT TOP 1
...
ORDER BY [fieldname]```
Something like that?

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
george, there are three fields (see post #1), not one

7. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Ohh of course - my bad.

so it's like this?

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

|__6___|__4__|__1___|
+------+------+------+

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
i see you are beginning to catch up, george

9. Registered User
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;

10. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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.

11. SQL Consultant
Join Date
Apr 2002
Location
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

12. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Originally Posted by r937
george, you are definitely on the right track
That's all I wanted to hear

13. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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*

14. SQL Consultant
Join Date
Apr 2002
Location
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

15. Registered User
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

#### Posting Permissions

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