Help with SQL LOT-Counter Query

General Discussion
#1

I have table called “Factory1” (MS SQL Server) containing columns LOT and Counter:

LOT Counter
87888 0
… …
87888 345768
87889 0
… …
87889 560787
etc.
During the shift, at the end of every batch the counter is reset. I have to write query showing the running (and finished batches if any) and the corresponding counter values at the end of every shift. Any hints?

#2

A query could look something like this:

SELECT LOT, MAX(Counter) FROM Factory1
GROUP BY LOT
ORDER BY LOT
#3

Thank you, JordanCClark.

#4

Should the table not be updated instead of inserted to every time, so that the 0 counter value is replaced with the actual counter number? Otherwise you’ll end up with double the data usage for (seemingly) no reason

#5

If it’s also stored with a timestamp, it can also be used to calculate efficiency trends and hourly counts. Not enough information to say from the available data.

#6

Thank you both for your new inputs. I use this script in report. It works perfectly, just as intended, I wonder how I was not be able to figure it out earlier - OK, my SQL knowledge is pretty basic.