asked on

How to a efficiently attatch an extra field with a MAX-aggregated field?

I have two tables ASSOC and JNL.
*********************************************************************************
ASSOC: assosciates the master equipments and slave equipments
*********************************************************************************
MASTER_ID (joint primary key), SLAVE_ID (joint primary key)
--------------------------------------------------------------------------
1 45
1 46
2 47
2 48

******************************************************
JNL: log the usage of the master equipments
******************************************************
MASTER_ID (foreign key) TIME_STAMP USAGE LOG_ID (primary key)
-----------------------------------------------------------------------------------------------
1 2008/10/01 100 5
1 2008/09/01 20 2
1 2008/05/01 100 1
2 2008/09/30 50 4
2 2008/09/10 80 3

I want to make a SQL query to return this table:
MASTER_ID, SLAVE_ID, TIME_STAMP USAGE
-----------------------------------------------------------------------------
1 45 2008/10/01 100
1 46 2008/10/01 100
2 47 2008/09/30 50
2 48 2008/09/30 50

The returned table should have the most recent usage date of master equipment for each association, together with the usage amount of that time.

I only find the following SQL statement getting the most recent time stamp, but don't know how to let the USAGE go with it.
*****************************************************************************************
SELECT ASSOC.MASTER_ID, ASSOC.SLAVE_ID, MAX(TIME_STAMP)
FROM ASSOC, JNL
WHERE ASSOC.MASTER_ID = JNL.MASTER_ID
GROUP BY ASSOC.MASTER_ID, ASSOC.SLAVE_ID
*****************************************************************************************

Do yo have any idea to get the USAGE together?

Thank you!

Cvijo123

if your log_ID is primary key auto identity why not use last ID inserted for your master_id as last usage ?
if that is case then you should use something like:

SELECT
	ASSOC.MASTER_ID,
	ASSOC.SLAVE_ID,
	JNL.USAGE, 
	JNL.TIME_STAMP
FROM ASSOC
	left join ( Select max(LOG_ID) as LOG_ID, MASTER_ID from JNL group by MASTER_ID ) lastEntry
		on ASSOC.Master_ID = lastEntry.Master_ID
	left join JNL
		on JNL.Log_ID = lastEntry.Log_id

Open in new window

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MarkusId

SELECT ASSOC.MASTER_ID, ASSOC.SLAVE_ID, max(TIME_STAMP), max(usage)
FROM test1 ASSOC, test2 JNL
WHERE ASSOC.MASTER_ID = JNL.MASTER_ID
  AND time_stamp = (SELECT max(i.time_stamp)
                       FROM test2 i
                      WHERE i.master_id = jnl.master_id)
GROUP BY ASSOC.MASTER_ID, ASSOC.SLAVE_ID
/

Open in new window

Sujith

MarkusId:
Your approach uses a correlated subquery. It will be slower as there are multiple FULL table scans of JNL.

MarkusId

I wasn't aware of that, isn't the sub-query to be evaluated as the last condition? And as there is a foreign key on master_id of JNL these shouldn't be full table scans.

However, I've experienced often enough that there are differences between the expected and the real behaviour of a database, so you might be right.

SELECT ASSOC.MASTER_ID, ASSOC.SLAVE_ID, max(i.MAX_TIME_STAMP), max(jnl.usage)
FROM (SELECT max(time_stamp) max_time_stamp, master_id
        FROM JNL
       GROUP BY master_id) i, ASSOC, JNL
WHERE ASSOC.MASTER_ID = JNL.MASTER_ID
  AND jnl.time_stamp = i.max_time_stamp
  AND jnl.master_id = i.master_id
GROUP BY ASSOC.MASTER_ID, ASSOC.SLAVE_ID
/

Open in new window

SOLUTION

dbmullen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

huangs3

ASKER

Cvijo123:
That's a good idea in practice, but strictly speaking we cannot garantee that primary key field follows the order of time.
Sui