Kbase P162554: SQL: How to SELECT the latest date and the latest time for each record.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
26/03/2010 |
|
Status: Unverified
GOAL:
SQL: How to SELECT the latest date and the latest time for each record.
GOAL:
How to SELECT the MAX date and MAX time for each record owner from a table?
GOAL:
How to find the most recent date and most recent time a record was created or modified form a price table, an employee table, a machine operator's time log table or any table that has a non unique ID and date and time fields?
GOAL:
How to select the latest date and time for each machine operator shift from a table named "PUB"."tm-log" that has three fields: "oper-no", "end-date" and "end-time" where "oper-no" is an INTEGER field to store a machine operator number and "end-date" is a DATE field to store the date the shift of that operator ended and "end-time" is an INTEGER field to store the time that shift ended as a number of seconds since midnight.
GOAL:
What SQL query would return the following Result Set:
oper-no end-date end-time
20 2010-02-02 41975
30 2010-02-16 32941
40 2010-02-16 43838
60 2010-02-17 32679
60 2010-02-17 32679
From the following sample data:
oper-no end-date end-time
20 2010-02-02 41975
30 2010-02-03 45906
30 2010-02-16 32941
40 2010-02-04 46099
40 2010-02-04 50227
40 2010-02-04 59466
40 2010-02-04 62024
40 2010-02-16 43838
60 2010-02-17 32679
60 2010-02-17 32679
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
One way to find the most recent date and most recent time an owner's record was created or modified form a price table, an employee table, a machine operator's time log table or any table that has a non unique owner ID and date and time fields is to execute a query along the following lines. In this example, each machine operator creates a record at the end of his/her shift. That record, includes, possibly amongst other information, the date and time the shift ended and the machinist number. This query returns for each machinist, the latest date and the latest time he created or modified a record in that time log table. The same logic can be used to obtain the latest or MAX values of control fields in any table of similar structure:
SELECT
"oper-no","end-date", "end-time"
FROM
"PUB"."tm-log"
WHERE
"end-date" IN (SELECT MAX("end-date") FROM "PUB"."tm-log" GROUP BY "oper-no") AND
"end-time" IN (SELECT MAX("end-time") FROM "PUB"."tm-log" GROUP BY "oper-no", "end-date")