I can not get MAX in query to work with date (timestamp).
I have Table-Production" records are production in different containers with timestamp.
I need to make a query showing status of each container now.
Let’s start with something simple:
SELECT "Container", MAX("Timestamp") AS "Latest", COUNT("Container") AS "Containers"
FROM "Production"
GROUP BY "Container"
Each record of a typical aggregation query represents multiple records of the source table. The columns are either calculated by an aggregation function (min, max, sum, count, etc.) or they are listed in the GROUP BY clause.
Any field not calculated nor grouped raises an error.
Any grouping by unique field (your order number) makes the aggregation useless because each resulting row represents only one record of the sourcec table.
1 Like
So if i want to find the latest productions in the 4 Container (20-23) and see what product and amount they are having at the moment. It’s not possible in a query, at least not with MAX? If only the data set is “Table-Production”
It is. With the query by @Villeroy you got a list of all containers with the max timestamp. Now you can JOIN this result with the original table to get access to the other columns.
.
And this is precisely the solution @cpb already suggested ( 3 hours before your post)
1 Like
no idea which database you use, this code is generic.
select p.*
from
(select "Container", max("Timestamp") mx_stamp from "Production" group by "Container") c
join
"Production" p
on c."Container" = p."Container"
and
c.mx_stamp = p."Timestamp"
order by "Order"
1 Like