MySQL find last of a group

My application concerns containers that are subject of a maintenance. This means that every container will be registrated IN at delivery and OUT after repair. The same container will be returned after doing his job, which is transport of chemical products, and the need of an inspection.

So we have a table with a key = containernumber + counter.
Every time the container comes back the counter will be incremented +1
So every containernumber is a group, composed by the historical evolution of this container.
The history of each container starts with counter 1
How can I find the highest key of a container.
Normally I would read the table with the key containernumber+ZZZ, which will set a pointer and read then the previous key.
But this is not possible with MySQL/

Any suggestions?

If you know the length of the container number this might work
SELECT FROM containers WHERE LEFT(containernumber, x) = ? ORDER BY containernumber DESC LIMIT 1

where x is the length of the container number & ? is the container number to search.

The container number and the counter must be different attributes in the relation (in SQL terms: different columns in the table).

The containernumber and counter are different columns in the table.
I think the suggestion of Wayne is great.

Is there something against the following?
SELECT max(ct_counter) from containers where ct_number = “ABCD 123456-7”

Then I misunderstood your OP. In that case I’d recommend two tables. Container – transport/in/out is a 1 : n relationship. Container is the master data table, and transport/in/out is the transaction table (like customer – invoices is).

I understand what your are suggesting, but this is not my problem.
I need to know what the highest number is of all moves of this container
Going back in this conversation we had already to solutions.

SELECT ct_counter SELECT WHEN ct_number = “ABCD 123456-7” ORDER BY ct_number DESC LIMIT 1
or
SELECT max(ct_counter) from containers where ct_number = “ABCD 123456-7”

I cannot say which one is the better one, or the fastest one.

Your problem is gone as soon as you have two tables.

How can a second table solve the problem of finding the last key in a group of records?

There is no need for maintaining a key or running count. You just count the entries in the transaction table:

SELECT COUNT(*) FROM Transactions WHERE Container_ID = ...

something like

SELECT ct_counter from containers where ct_number = “ABCD 123456-7” order by ct_counter desc limit 1

this grabs all the counters for a specific container in descending order and just returns the first one

SELECT COUNT(*) FROM Transactions WHERE Container_ID = …
this is not okay. If someone would delete a line then the counter would be wrong

SELECT ct_counter from containers where ct_number = “ABCD 123456-7” order by ct_counter desc limit 1
This was mentioned before by Wayne

SELECT max(ct_counter) from containers where ct_number = “ABCD 123456-7”
This seems the most logic but maybe not the most efficient?

select max is likely slightly more efficient
esp if you have an index on the container number and counter columns

Norman, are you saying that ordering is slower than calculating a max, even when there is an index?
Now I’m surprised.

Of course we are not talking about millions of records.

[quote=289008:@roland brouwers]SELECT COUNT(*) FROM Transactions WHERE Container_ID = …
this is not okay. If someone would delete a line then the counter would be wrong[/quote]
Usually users (and also admins) should in business applications not have the possibility to delete or alter any transaction records.

[quote=289012:@roland brouwers]Norman, are you saying that ordering is slower than calculating a max, even when there is an index?
Now I’m surprised.

Of course we are not talking about millions of records.[/quote]
It could be
Max, eps with an index on the column that you want the max of, can be optimized in ways that make it very fast
The db engine can just grab that one value very quickly
“order by” has to grab all results, sort them into the required order, return the result set … oh and then only return 1 of them
Now there may be specific optimizations that mysql performs for this but I havent read the entire book on the mysql optimizer to know for sure

I totally agree with you, Eli. But in the container business you will only find Cowboys.

I thank you for the explanation, Norman. It has given me more confidence

MAX, AVG, SUB etc… has to grab ALL the results as well, however the differnece is THEY don’t care about the ORDER, where ORDER BY has the added overhead of having to rearrange the output recordset.

And just because you defined a particular index, does NOT mean a given query will use it (only if the optimizer THINKS it should, and not all optimizers are as good as they should be)

so, we can conclude that

SELECT max(ct_counter) from containers where ct_number = "ABCD 123456-7"

is the best, most efficient, solution?

[quote=289022:@roland brouwers]so, we can conclude that

SELECT max(ct_counter) from containers where ct_number = "ABCD 123456-7"

is the best, most efficient, solution?[/quote]
if you are seeking on one specific container, then yes
otherwise you need to resort to a GROUP BY (with an optional ORDER BY)

SELECT ct_number,max(ct_counter) as ct_counter from containers GROUP by ct_number ORDER BY ct_number)

this will return ALL containers, their max(counter)
it obviously has a touch more over header, but the ORDER BY only has to deal with the results not the entire table
and in this case a CT_NUMBER index would probably help

Wrong Dave
Many built in aggregates can be optimized in very specific ways
I’m quite sure rolands use is precisely one that CAN be optimized
Mysql has certain specific optimizations for aggregates (as many db’s do) that can reduce the results to only grabbing ONE row
http://dev.mysql.com/doc/refman/5.7/en/where-optimizations.html
Here’s MariaDB’s optimization of MAX ( https://mariadb.com/kb/en/mariadb/minmax-optimization/ )
Oracle and Sybase also have similar optimizations they can do
http://docs.oracle.com/javadb/10.8.3.0/tuning/ctunoptimz22111.html
https://www.postgresql.org/docs/current/static/sql-createaggregate.html