Solved Retrieving the record with the highest column field number value or return "No Value" if exist.... (1 Viewer)

[6thDAY]

I have a simple table containing sample data like so:

Color	Purity
Red	10
Red	3
Red	9
Red	No Value
Red	12
Red	11

I'm trying to run a query where if there are multiple of the same Color, pick the record that has the highest Purity value, but ONLY if there doesn't exist a record that contains a Purity = No Value.

For example, in the above, I would like to return the record:

Color	Purity
Red	No Value

Here's my SQL:

SELECT TOP 1 Color, Purity
FROM Table1
WHERE Purity <> 'No Value' AND Color = [Table1].[Color]
ORDER BY CInt(IIF(Purity='No Value','No Value',Purity)) DESC;

But what I'm getting is:

If I have the following table with no No Value:

Color	Purity
Red	10
Red	3
Red	9
Red	12
Red	11

Then I get the correct return Query record:

Color	Purity
Red	12

The data type of Purity is a Short Text. How can I set the query to return the record if there is a 'No Value' Purity, otherwise return the record with the highest value?

 

[arnelgp]

you need to Create 3 queries:
1. Query1:
SELECT TOP 1 Table1.Color, Table1.Purity
FROM Table1
WHERE (((DCount("1","Table1","Color = '" & [Color] & "' And Purity = 'No Value'"))=0))
ORDER BY Val(Purity) DESC;

2. Query2:
SELECT TOP 1 Table1.Color, Table1.Purity
FROM Table1
WHERE Purity="No Purity";

3. your Final Query using Union Query:
SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2;

 

[6thDAY]

Instead of using 3 queries, I did this:

SELECT TOP 1 Color, Purity
FROM Table1
ORDER BY CInt(IIF(Purity='No Value',1000,Purity)) DESC;

Where 1000 is guarantee to be the highest value even though it will never reach it, and I was able to return the record containing No Value. Is this OK code implementation or is it more of a backdoor hack to "make" it work?

 

[arnelgp]

correction on Query2:

Where Purity = "No Value";

 

[arnelgp]

imho, you use Val()

?Val("No Value") = 0
?Val("12") = 12

 

[Gasman]

@arnelgp
SHouldn't that be

correction on Query2:

Where Purity = "No Value";

Ah, was just going to ask you that

 

[arnelgp]

Ah, was just going to ask you that

purity without purity is unimaginable.

 

[6thDAY]

imho, you use Val()

?Val("No Value") = 0
?Val("12") = 12

Apologies, I don't follow you in relation to my "hack" solution?

 

[Pat Hartman]

Change the data type of the purity to integer. Strings do not work the same way as numbers when you are sorting or looking for the largest value. In the list shown, the largest value is 9 if the field is a string but 12 if the field is numeric. Why" Strings are compared character by character, left to right and 9 is > 3 and 1 which are the other leading characters.

Your solution from #3 will work but only if you change the data type. Rather than trying to use a string in a numeric field, let the value be null instead.

SELECT TOP 1 Color, Purity
FROM Table1
ORDER BY Nz(Purity, 1000) DESC;

The Nz() function (Null to 0) converts a null value to what ever you tell it to. In this case, you want a number higher than any natural value for the field so 1000 will work. It could be 9999999, if 1000 is a potential natural value.