Max of date not working

[DRANDON]

Hello. I have a query where I am doing Totals to get the Max date. The table has dates of 1/1/2018, 4/1/2018, 7/1/2018, and 10/1/2018. Each date may or may not have a different price for any item. When I do MAX in the date Total row I get 1/1/2018 AND 10/1/2018. Not just 10/1/2018. Price is "Group by" in the Total row.

In the results any item will have a different Price for 1/1/2018 than 10/1/2018.

If I do a MIN I get 1/1/2018 AND 4/1/2018. I am lost.

I have another query altogether with a different table using MAX and Group by Price and it works fine.

Please HELP.

Thank you so much.

 

[xenou]

If there are other columns in the query then the max may not work as you expect. Max is calculated for each group. The group that is the entire table would give only one max. A group that is only an item also has one max (per item). But it gets harder to keep this up when you add more columns (for instance, if you then include a column ShipDate you will have one max - but now per item, per ship date. At some point you will be unable to include more columns and still get the max you want to calculate. Usually you use two queries to resolve this problem -- one of them dedicated to getting the max you need).

Post your entire query if you need further explanation (note that sometimes columns included in a GROUP BY clause impact results, even if they are not in the SELECT clause!)