SQL Subselect with MAX in where clause convert to Informix

Question

I have following query in MSSQL :

select
    c1.auftrnr
    ,MAX(DATUM) as Datum
    ,(select MAX(Zeit) from kaufedit where auftrnr=c1.auftrnr and datum=max(c1.datum)) as Zeit
from kaufedit c1
group by c1.auftrnr
order by c1.auftrnr

This selects for every order the maximum day , and for that maximum day the maximum time . In order to check if something changed. This works really good and fast.

I am trying to convert this into Informix , but till now I was unable to make it work .

UPDATE 1

12.10.FC14WE is the version datum is date(10) zeit is char(5) both cannot be changed :(

I have tried following :

select extend(datum, year to second)+(zeit - DATETIME(00:00) hour to minute) as datumzeit from kaufedit

Answer 1

It would be best, IMO, if you combined the Datum and Zeit columns into a single DATETIME YEAR TO SECOND value. You would simply need the maximum of the combined column for each order.

However, assuming that's not an option and that you do have a sufficiently recent version of Informix, then you can use a CTE — common table expression — like this:

WITH t1 AS
    (SELECT auftrnr, MAX(datum) AS datum
       FROM kaufedit
      GROUP BY auftrnr
    )
SELECT c1.auftrnr,
       c1.datum,
       MAX(c1.zeit) AS zeit
  FROM kaufedit AS c1
  JOIN t1 ON t1.auftrnr = c1.auftrnr AND t1.datum = c1.datum
 GROUP BY c1.auftrnr, c1.datum
 ORDER BY c1.auftrnr, c1.datum;

The CTE derives the order number and maximum date for the order. The main query then joins that result with the main table, yielding the desired output.

Schema and Data The data format assumes that you set DBDATE='Y4MD-' or equivalent in the environment. Alternatively, change the DATE type to DATETIME YEAR TO DAY.

DROP TABLE IF EXISTS kaufedit;

CREATE TABLE kaufedit
(
    auftrnr     INTEGER NOT NULL,
    datum       DATE NOT NULL,
    zeit        DATETIME HOUR TO SECOND NOT NULL,
    information VARCHAR(32) NOT NULL
);

INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(123, '2022-01-01', '01:23:45', 'Entered at 2022-01-01T01:23:45');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(123, '2022-01-01', '10:22:44', 'Entered at 2022-01-01T10:22:44');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(123, '2022-01-01', '11:32:54', 'Entered at 2022-01-01T11:32:54');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(123, '2022-01-01', '21:17:05', 'Entered at 2022-01-01T21:17:05');

INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(321, '2022-01-02', '01:23:45', 'Entered at 2022-01-02T01:23:45');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(321, '2022-01-02', '17:22:44', 'Entered at 2022-01-02T17:22:44');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(321, '2022-01-03', '11:32:54', 'Entered at 2022-01-03T11:32:54');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(321, '2022-01-03', '11:57:05', 'Entered at 2022-01-03T11:57:05');

INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(444, '2022-02-02', '01:23:45', 'Entered at 2022-02-02T01:23:45');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(444, '2022-03-02', '10:22:44', 'Entered at 2022-03-02T10:22:44');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(444, '2022-04-03', '11:32:54', 'Entered at 2022-04-03T11:32:54');
INSERT INTO kaufedit(auftrnr, datum, zeit, information)
    VALUES(444, '2022-05-03', '21:57:05', 'Entered at 2022-05-03T21:57:05');

Output

123  2022-01-01  21:17:05
321  2022-01-03  11:57:05
444  2022-05-03  21:57:05

(Tested on Linux RHEL 7.4 with Informix 14.10.FC1.)

---

Extension

If you want the extra information in the kaufedit table, then you might use a query such as:

WITH t1 AS
    (SELECT auftrnr, MAX(datum) AS datum
       FROM kaufedit
      GROUP BY auftrnr
    ),
     t2 AS
    (SELECT c1.auftrnr, c1.datum, MAX(c1.zeit) AS zeit
       FROM kaufedit AS c1
       JOIN t1 ON t1.auftrnr = c1.auftrnr AND t1.datum = c1.datum
      GROUP BY c1.auftrnr, c1.datum
    )
SELECT c1.auftrnr, c1.datum, c1.zeit, c1.information
  FROM kaufedit AS c1
  JOIN t2
    ON c1.auftrnr = t2.auftrnr
   AND c1.datum = t2.datum
   AND c1.zeit = t2.zeit
 ORDER BY c1.auftrnr, c1.datum;

Output

123  2022-01-01  21:17:05  Entered at 2022-01-01T21:17:05
321  2022-01-03  11:57:05  Entered at 2022-01-03T11:57:05
444  2022-05-03  21:57:05  Entered at 2022-05-03T21:57:05