Ingres SQL, find the max value of one column based on the value of another column

Question

I'm working on an Ingres DB with a script I've inherited from someone else. I need to change the script to pull out the action_times of the latest start_time and end_time event, and also the difference between the two. A sample of the DB is listed below

id_num  | version  | action_id  |   action_time
----------------------------------------------------------------------------
1         2          start_time     2014-05-26 14:58:14
1         2          end_time       2014-05-26 14:58:16
1         4          start_time     2014-05-27 10:10:57
1         4          end_time       2014-05-27 10:10:11

So far what I've come up with is:

SELECT max(a.action_time) as BIG, max(b.action_time) as SMALL, max(a.action_time) - max(b.action_time) as DIFF
FROM table1 as a, table1 as b,
WHERE a.id_num = '1' AND a.action_id = 'end_time' AND b.id_num = '1' AND b.action_id = 'start_time'

but the results are coming out as follows:

BIG                         SMALL                   DIFF
----------------------------------------------------------------------------
2014-05-27 10:10:11         2014-05-27 10:10:57     null

Apologies if a question like this has already been answered (I'm sure it probably has) but I've spent a couple of days looking over various forums and I can't find a similar example, probably how I'm phrasing the search terms. Any help would be much appreciated, I'm pretty sure I would have covered something like this in college but that was a few years ago and my SQL is a bit rusty these days. Thanks in advance!

Edit: So after some research I have come up with the following which will work in the DB GUI:

SELECT ingresdate(varchar(max(a.action_time))) as BIG, ingresdate(varchar(max(b.action_time))) as SMALL, date_part('secs',ingresdate(varchar(max(a.action_time))) - ingresdate(varchar(max(b.action_time)))) as DIFF
FROM table1 as a, table1 as b,
WHERE a.id_num = '1' AND a.action_id = 'end_time' AND b.id_num = '1' AND b.action_id = 'start_time'

The documentation seems to suggest this should be valid. What version of Ingres? What are the underlying types? Are you sure `action_time` isn't character (granted, I'd expect to get an error in that case...)? What happens if you try to cast/add an interval? What do you want the resulting difference to look like? I'd recommend doing this as a pair of subqueries - as it is you have a large Cartesian product from the join, and end up throwing most of the rows away. — Clockwork-Muse, Jun 08 '14 at 01:38
I'll have to check what version of Ingres it is when I'm back in work tomorrow. — John Kilo, Jun 08 '14 at 15:37

score 0 · Answer 1 · answered Jun 08 '14 at 01:15

0

If you want to calculate the difference between max(a.acction_time), and max(b.acction_time) you should use the following script:

SELECT max(a.acction_time) as BIG, max(b.acction_time) as SMALL,DATEDIFF(s, max(a.acction_time), max(b.acction_time)) as DIFF
FROM table1 as a, table1 as b
WHERE a.id_num = '1' AND a.action_id = 'end_time' AND b.id_num = '1' AND b.action_id = 'start_time'

If you do not remember DATEDIFF() function i will explain it for you.

P.S: where is the Primary key in your table1?!!

answered Jun 08 '14 at 01:15

Ziad Salem

496
13
34

The OP's table appears to be some sort of history/log table which often don't have unique-per-row keys - usually a combination of the original table's unique key and updated timestamp will be unique (even if not constrained). Unfortunately the db the OP is using doesn't appear to have a `DATEDIFF` function, although it appears the basic form of what the OP is trying is valid. – Clockwork-Muse Jun 08 '14 at 01:36
Yes, while the table I provided above is an simplified version, there is no primary key, only a composite key. Clockwork-Muse is correct, it is a log table. – John Kilo Jun 08 '14 at 15:39
I guess the table has a composite primary key made out of `id_num` and `version` attributes. – DejanLekic Jun 13 '14 at 12:00
I don't guess that because the primary key (id_num,version) that you suggest is duplicated – Ziad Salem Jun 13 '14 at 20:27

score 0 · Answer 2 · answered Jul 24 '14 at 20:48

I would use sub-selects for this. Try :-

select a.action_time as max_end_time, b.action_time as max_start_time, 
a.action_time - b.action_time as diff
from table a, table b

where a.action_time = (select max(action_time)
from table where action_id = 'end_time')

and b.action_time = (select max(action_time)
from table where action_id = 'start_time)

Adrian · Answer 3 · 2014-08-05T08:34:34.237

Here is my attempt:

SELECT start.action_time, end.action_time, 
  interval('seconds', end.action_time - start.action_time ) as diff_secs
FROM
(
SELECT action_time
FROM table a
INNER JOIN 
(  SELECT max(id_num) as max_id_num, max(version) as max_version FROM table 
) b on ( id_num = max_id_num and version = max_version )
WHERE a.action_id = 'start_time'
) start
CROSS JOIN
(
SELECT action_time
FROM table a
INNER JOIN 
(  SELECT max(id_num) as max_id_num, max(version) as max_version FROM table 
) b on ( id_num = max_id_num and version = max_version )
WHERE a.action_id = 'end_time'
) end

Using your data I get the following output:

+----------------------+----------------------+-----------+
|     action_time      |     action_time      | diff_secs |
+----------------------+----------------------+-----------+
| 27-May-2014 10:10:57 | 27-May-2014 10:10:11 |       -46 |
+----------------------+----------------------+-----------+

For reference, here is the script I used to create and populate the test table

CREATE TABLE table
(
id_num integer,
version integer,
action_id char(10),
action_time timestamp
)

INSERT INTO table VALUES (1,2,'start_time', '2014-05-26 14:58:14');
INSERT INTO table VALUES (1,2,'end_time', '2014-05-26 14:58:16');
INSERT INTO table VALUES (1,4,'start_time', '2014-05-27 10:10:57');
INSERT INTO table VALUES (1,4,'end_time', '2014-05-27 10:10:11');

Ingres SQL, find the max value of one column based on the value of another column

3 Answers3