Solved

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

Posted on 2008-10-30
7
285 Views
Last Modified: 2013-12-19
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!
0
Comment
Question by:huangs3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22846434
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

0
 
LVL 27

Accepted Solution

by:
sujith80 earned 400 total points
ID: 22847147
Use this query.
select X.master_id, X.slave_id, Y.time_stamp, Y.usage
from 
ASSOC X, 
( 
select master_id, time_stamp, usage
from (
select master_id, time_stamp, usage, row_number() over(partition by master_id order by time_stamp desc) rn
from JNL)
where rn = 1
) Y
where X.master_id = Y.master_id;

Open in new window

0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22848349

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

0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 27

Expert Comment

by:sujith80
ID: 22848609
MarkusId:
Your approach uses a correlated subquery. It will be slower as there are multiple FULL table scans of JNL.
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22848859
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

0
 
LVL 10

Assisted Solution

by:dbmullen
dbmullen earned 100 total points
ID: 22850179
same as sujith80 but with less lines.
if you really want to see what it does.
run this as a test
SELECT x.master_id, x.slave_id, y.time_stamp, y.USAGE,
        ROW_NUMBER () OVER (PARTITION BY y.master_id
                            ORDER BY y.time_stamp DESC) rn
          FROM jnl y, assoc x
         WHERE x.master_id = y.master_id

SELECT master_id, slave_id, time_stamp, USAGE
  FROM (SELECT x.master_id, x.slave_id, y.time_stamp, y.USAGE,
        ROW_NUMBER () OVER (PARTITION BY y.master_id 
                            ORDER BY y.time_stamp DESC) rn
          FROM jnl y, assoc x
         WHERE x.master_id = y.master_id)
 WHERE rn = 1
;

Open in new window

0
 

Author Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question