Is MAX Case sensitive?

[TOPSie]

I have a table with 3 fields [Item] [Flag] [Date]


[Flag] is single character. I am trying to write a query to extract the latest [Date] an [Item] had a [Flag] containing the letter L (uppercase).
Unfortunately the letter l (lowercase L) is also used as a [Flag]

So my query is
SELECT {Item], [Flag], Max([Date]) FROM [Table] GROUP BY <<fields>>
HAVING [Flag] LIKE “L”;

I expect to get one return for each item, but what I get is
Apple L 19/2/2024
Apple l 3/4/2019
Pear L 1/1/2023
Pear l 6/7/2016

So whilst the LIKE is case insensitive somewhere in the logic of MAX it is treating the match as case sensitive.

Can anyone explain how to get the latest value of just either L or l ??

[CJ_London]

weird sql syntax - please specify what database you are using that uses that syntax or provide your actual sql. But my guess would be you have other fields you are not showing

[TOPSie]

weird sql syntax - please specify what database you are using that uses that syntax or provide your actual sql. But my guess would be you have other fields you are not showing

I was trying to paraphrase the syntax - the real query is much more complicated - so I was just trying to pick out the essence of the issue - so a sort of psuedo SQL

[Minty]

Just post up the real SQL. We will be guessing without seeing it.

[TOPSie]

Just post up the real SQL. We will be guessing without seeing it.

SELECT myTable.myItem, mytable.myFlag, myTable.Comment, Max(myTable.myDate) AS MaxOfMyDate
FROM myTable 
GROUP BY  myTable.myItem, myTable.myFlag, myTable.Comment,  myTable.MyDate
HAVING myTable.myFlag LIKE"L";

I have stripped the SQL to the bare minimum to illustrate the point and not introduce other fields and criteria to confuse the issue.
This exact SQL will return just two lines for each item which as multiple records containing flags or L (uppercase) or l (lower case)

[Micron]

I don't see how Max is relevant for L vs l. You're using Max on a date field, not the flag field.

[Micron]

I think what you need is
HAVING StrComp("L",myTable.myFlag,0)=0

[TOPSie]

I don't see how Max is relevant for L vs l. You're using Max on a date field, not the flag field.

I think this is the point I want it to find all the items with a Flas of "L" then select the record form these items which has the latest date. But the return is the record with the latest date for Flag = "L" AND the latest date for Flag = "l"

[TOPSie]

I think what you need is
HAVING StrComp("L",myTable.myFlag,0)=0

This works. So THANK YOU - but still bemused as to the results when I use LIKE or just =

A mystgery for another day :-)

[CJ_London]

would have thought

SELECT myTable.myItem, Max(myTable.myDate) AS MaxOfMyDate
FROM myTable 
WHERE myTable.myFlag LIKE "L"
GROUP BY  myTable.myItem

would give you what you want

[Micron]

I'm not sure what you mean or what the mystery is. Access sql is not case sensitive, so your query doesn't care if the field contains L or l. If you have a record with L and a record with l, you will get the Max date for each. At most you should get two records, even if you have 5 records with L and 5 with l as Max will pick the Max date for each case.

[Micron]

would have thought

SELECT myTable.myItem, Max(myTable.myDate) AS MaxOfMyDate
FROM myTable 
WHERE myTable.myFlag LIKE "L"
GROUP BY  myTable.myItem

would give you what you want

If that is true then am I wrong about Access sql not being case sensitive? Not sure now.

[TOPSie]

. If you have a record with L and a record with l, you will get the Max date for each. At most you should get two records, even if you have 5 records with L and 5 with l as Max will pick the Max date for each case.

This is exactly what I got - which contradicts your assumption that SQL is case insenstive!!???

[CJ_London]

If that is true then am I wrong about Access sql not being case sensitive? Not sure now.

No you are not wrong, Access SQL is not case sensitive. T-SQL can be if you use the right syntax (Collate I seem to recall) but otherwise is also not case sensitive

If you have a record with L and a record with l, you will get the Max date for each.

that is wrong, you will get one record

OP is bringing through many more fields which is causing the 'duplication' which is what I guessed in the first place in post #2

SELECT myTable.myItem, mytable.myFlag, myTable.Comment, Max(myTable.myDate) AS MaxOfMyDate
FROM myTable 
GROUP BY  myTable.myItem, myTable.myFlag, myTable.Comment,  myTable.MyDate
HAVING myTable.myFlag LIKE"L";

There is a comment field returned (not shown in the cut down version in post #1) and also a grouping on myDate which I would have thought all unique records would be returned - implication being there are only two records per fruit if what the OP gets as stated in post #1 is to be believed.

The Flag being returned is irrelevant - just a coincidence the one is uppercase and the other lowercase.

Also, no need for Like, an = will do since the field (presumably) only contains a single character, at least for the data they are interested in

if you want to get separate rows for upper and lowercase, you would need to do an ascii grouping i.e.

GROUP BY..... asc(myFlag)