how return max value from one of three "fields" in a query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running a query to compare 3 fields and I want only the information in
the field with the highest number displayed in the output.
 
try to select "Max" in the Totals row for the field you want to display the
higher number
 
Ginycub22 said:
I am running a query to compare 3 fields and I want only the information in
the field with the highest number displayed in the output.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SELECT IIf(col_1 > col_2, col_1, IIf(col_2 > col_3, col_2, col_3)) As
LargestNumber
FROM ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRKwjL4echKqOuFEgEQLAqQCgmgSeJrhlRKrflZPKI8Gv9UEhoq4AoPco
wstP/VTm7kfA0rS/0q7NoRJS
=WQ7r
-----END PGP SIGNATURE-----
 
That you are comparing data from three fields shows that you have a serious
normalization problem. As such, the SQL to do what you say will be ugly.
Here's proof of that!

SELECT *
FROM YourTable
WHERE PrimaryKeyField in
(Select PrimaryKeyField from
(SELECT TOP 1 U.PrimaryKeyField, Max(U.MaxNum) AS MaxOfMaxNum
FROM (SELECT PrimaryKeyField, [Field1] as MaxNum FROM YourTable
UNION ALL
SELECT PrimaryKeyField, [Field2] FROM YourTable
UNION ALL
SELECT PrimaryKeyField, [Field2] FROM YourTable) AS U
GROUP BY U.PrimaryKeyField
ORDER BY 2 Desc));

Possibly someone has a much prettier solution, but none could approach a
properly normalized table structure.
 
SELECT IIf(col_1 > col_2, col_1, IIf(col_2 > col_3, col_2, col_3)) As
LargestNumber
FROM ...
Click to expand...

Up to a point. From the Immediate pane:

col_1 = 1
col_2 = 0
col_3 = 2
?IIf(col_1 > col_2, col_1, IIf(col_2 > col_3, col_2, col_3))
1

Maybe
IIf(IIf(col_1 > col_2, col_1, col_2) > col_3, IIf(col_1 > col_2, col_1,
col_2), col_3)
 
Normalization issues aside... using IIF should do the trick.

I tried it using a sample table that I called tblNumbers with Num1,
Num2 and Num3 as my field names. It worked with no problem. Here's
the SQL

SELECT
IIf([Num1]>[Num2] And [Num1]>[num3],[num1]) AS Expr1,
IIf([num2]>[num1] And [num2]>[num3],[num2]) AS Expr2,
IIf([num3]>[num1] And [num3]>[num2],[num3]) AS Expr3
FROM tblNumbers;

As you can see I didn't bother to give meaningful names to the
expressions, hence the Expr1, Expr2 and Expr3.

Should someone compare three numbers? Well, I guess I can imagine a
scenario where they might, like a listing of items that each have a
StartValue, EndValue and ValueGoal. Those could be three discrete
values stored for a single record that would seem acceptable to me.
Just a thought. (Although as soon as I click "send" it will become
clear to me why I'm wrong...)

Betsy
 
IIF(Col1>col2 and Col1>Col3,Col1,IIF(Col2>Col3,Col2,COl3))

This should work UNLESS some of the columns contain nulls. Then things may be a
bit trickier.
 
See the MaxOfList() function here:
http://allenbrowne.com/func-09.html

In general, if you are comparing values across fields, it is worth
considering whether it would be better to create a related table where these
can be multiple records instead of having multiple fields in one table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

news:D[email protected]...
 
Back
Top