Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-30
7
Medium Priority
?
293 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
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 1600 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 400 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

572 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