Max value

[ScottXe]

I have a query challenge and your advice.

In my transaction table, I would like to find out the latest transaction dates of each family models. It sounds a simple Max function can get the desired result. However model number consists of the first 6 characters for the family model and the rest for the versions (variants). The Max function fails to work in this scenario. Is there any other way to group the model numbers once the 1st 6 characters are identical (the rest is ignored)?

 

[plog]

Discrete pieces of data need to be stored discretely. If the first 6 characters represent something, they should be stored by themselves. You wouldn't have 1 field for someone's entire name, you would have a field for each name part (First, Middle, Last, etc.).

If the 1st 6 characters represent something then they should be stored seperate from the rest of the value they are currently with.

 

[ScottXe]

As per your advice, I will create a calculated field to extract the first 6 characters and group it to find the max value. Thanks!

 

[pbaldy]

As an fyi, this should work:

SELECT Left(FieldName, 6) As LeftSix, Max(...)
FROM...
GROUP BY Left(FieldName, 6)

Though it wouldn't be terribly efficient.