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

[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.

 

[Guest]

try to select "Max" in the Totals row for the field you want to display the
higher number

 

[MGFoster]

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.

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

 

[Guest]

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.

 

[John Nurick]

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

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)

 

[betwalk]

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

 

[John Spencer]

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.

 

[Allen Browne]

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[email protected]...