Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query MAX JOIN UPDATE

Posted on 2008-06-21
16
Medium Priority
?
884 Views
Last Modified: 2011-10-19
Have two tables:  A and B
Table A = TimeStamp, TAG(KEY), Location, Tech, Model, Serial, EmployeeID
Table B = TimeStamp, Tag, Location

Need to UPDATE 3 fields in A based on on 3 Fields in B
Notes: TimeStamp in A will be the old TimeStamp, TimeStamp in B will the the newer TimeStamp, TAG in A is PRIMARY KEY, Tag in B is NOT Primary and may appear in several records with a different timestamp. Location will be various entries.

Example of Table B:
TAG      TimeStamp                           LOCATION
CC000417      5/20/2008 3:38:43 PM      A
CC000418      5/20/2008 3:47:02 PM      B
CC000418      5/22/2008 8:49:58 AM      A
CC000419      5/20/2008 3:49:25 PM      C
CC000419      5/28/2008 7:13:58 PM      A

After running SQL Query, 3 records in Table A should be updated as Follows:

CC000417      5/20/2008 3:38:43 PM      A
CC000418      5/22/2008 8:49:58 AM      A
CC000419      5/28/2008 7:13:58 PM      A

Here is just one attempt of about 50 that do not work - get an aggregate error.  Please help, perhaps this is not as easy as it would appear.
SELECT LINK.TAG, max(TimeStamp), LINK.LOCATION
FROM LINK
order  BY LINK.TAG, LINK.TimeStamp DESC;
0
Comment
Question by:dcorleto
[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
  • 8
  • 7
16 Comments
 
LVL 10

Expert Comment

by:Marcjev
ID: 21837716
Use something like this:
*determine the tag and max timestamp
*join this with the orginal table to only retrieve the records you need

(read timest  = timestamp...)
[remark: i tried this using ms sql 2005, but approach should also work in access though)

select tableb.tag, tableb.timest, tableb.location
from tableb
join (  select tag, max(timest) timest from tableb
            group by tag ) as maxtagst
on tableb.Tag = maxtagst.tag
and tableb.timest = maxtagst.timest
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21837723
>Max< is an aggregat function which requires you to group your rows. Try:

SELECT L.TAG, max(L.TimeStamp), L.LOCATION
FROM LINK AS L
GROUP BY L.TAG, L.LOCATION
ORDER  BY L.TAG, L.TimeStamp DESC;
0
 

Author Comment

by:dcorleto
ID: 21837763
Badotz:

Still aggregate error with that code useing Access 2003
Capture1.JPG
0
Implementing Azure Infrastructure Exam 70-533

This course is designed to familiarize and instruct students in the content that is covered by Microsoft Exam 70-533, Implementing Microsoft Azure Solutions. It focuses on all the November 2016 objective domain topics.

 

Author Comment

by:dcorleto
ID: 21837771
Can you please tell me what this means?  (read timest  = timestamp...)

and how I would or do I need it somewhere in the query?
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21837784
What kind of field is >TimeStamp< ??
0
 

Author Comment

by:dcorleto
ID: 21837787
TimeStamp is Date/Time
0
 
LVL 29

Accepted Solution

by:
Badotz earned 2000 total points
ID: 21837822
This will select the 'A' rows, but it does not update table 'A':

SELECT b.TAG, b.TimeStamp, b.LOCATION
FROM LINK_B AS b INNER JOIN [SELECT tag, max(timestamp) AS MaxOfTimeStamp FROM LINK_B GROUP BY tag; ]. AS maxtagst ON (b.TAG = maxtagst.tag) AND (b.TimeStamp = maxtagst.MaxOfTimeStamp);
0
 

Author Comment

by:dcorleto
ID: 21837835
YES YES  - SO CLOSE!!
I updated the syntax - now just need to update that info into Table A?  Can it be done?

SELECT b.TAG, b.TimeStamp, b.LOCATION
FROM LINK AS b INNER JOIN [SELECT tag, max(timestamp) AS MaxOfTimeStamp FROM LINK GROUP BY tag; ]. AS maxtagst ON (b.TimeStamp = maxtagst.MaxOfTimeStamp) AND (b.TAG = maxtagst.tag);
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21837843
Sorry - Access mungs the SQL so it is both unrunnable and uneditable. For this reason, *never* veiw this query in the Query Designer - always view it in SQL View. In fact, keep the query handy by copying it into a .SQL file you keep in the same directory as your .MDB file:

SELECT b.tag, b.timestamp, b.location
FROM LINK_B AS b
INNER JOIN (select tag, max(timestamp) AS maxtimestamp from LINK_B group by tag) as maxtag
ON b.tag = maxtag.tag and b.timestamp = maxtag.maxtimestamp;
0
 

Author Comment

by:dcorleto
ID: 21837867
Seems ok in Access 2003 - I can view and run in design mode.  How would I then get the info to update Table A?
Capture2.JPG
0
 

Author Closing Comment

by:dcorleto
ID: 31469413
I changed it to a Make Table Query and then create a macro to update from there - it seems to work - do I need to worry about corrupted data?
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21838040
>>do I need to worry about corrupted data?

Why would you think there is corrupted data? Access only mungs the SQL, *not* the data.

btw: "mung" is an ancient (well...) self-referential acronym for "mung until no good", which means "apply a method over and over until the results are meaningless or unrecognizable, or both". It usually occurs inadvertently due to bugs in your code.
0
 

Author Comment

by:dcorleto
ID: 21838078
Did you get the points?  Hope I did that right - thanks so much for your help  - this has drove me crazy for several weeks...with some tweaks I'll have it up and running..and thanks for the definition!!
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21838093
Yes, I got the points.

But since I cannibalized the SQL, shouldn't some of the points have been awarded to Marcjev, too?
0
 

Author Comment

by:dcorleto
ID: 21838131
didn't hear back about questions read timest  = timestamp...) in the meantime, yours worked.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 21838135
OK...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

715 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