Returning only the highest value

[woo8118]

Hello, I have a query that will populate the correct names but I'm trying to trim down some of them where the last 3 digits are completely random.



For instance say I have the names
cat1.103
cat1.104
cat4.110
cat4.120

The query (like "cat*.1*") returns all 4, but I only care about the highest value of the last 3 digits.. so I would like the query to return cat1.104 and cat4.120

Any help would be greatly appreciated

[ItsMe]

You will have to parse the number portion and order that. I suspect you will have to use an alias and cast the parsed value to a number data type before employing an ORDER BY Keyword.

[June7]

The highest value of the 3 digits is 120 so why do you want 104? You want the highest suffix value for each prefix?

SELECT Left([fieldname],4) AS Category, Max(Mid([fieldname],6)) AS MaxSuffix FROM tablename GROUP BY Left([fieldname],4);

[woo8118]

My apologies, yes the highest suffix value for each prefix

[June7]

Does query work?

[woo8118]

got it thanks