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
Solved

Special Join scenario

Posted on 2010-11-19
5
385 Views
Last Modified: 2012-05-10
I'm trying to find a performant solution for a special JOIN, but don't know how to solve it. This JOIN needs lots of performance and is surely not optimal.

Here it is: For each customer (tblCustomer) there exists a colour, but this colour can change from day to day, and the history of all colours must be tracked. So we have:

tblCustomer: ID
tblColour: IDCustomer, colour, timestamp

What I need is the most current colour for the customer.

So my current JOIN goes like this:

 
SELECT tblCustomer.ID , C2.colour
FROM  tblCustomer (NOLOCK) JOIN tblColour AS C2 ON tblCustomer.ID = C2.IDCustomer 
          JOIN (SELECT C1.IDCustomer, MAX(C1.timestamp) AS MaxTime FROM tblColour AS C1 GROUP BY C1.IDCustomer) AS T1 ON (T1.MaxTime = C2.timestamp) AND (T1.IDCustomer = C2.IDCustomer)  

Open in new window


Any idea welcome
0
Comment
Question by:PC-Alex
5 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 34173344
this article should help you solve the issue:
http://www.experts-exchange.com/A_3203.html
0
 

Expert Comment

by:jat0818
ID: 34175049
try this
select t2.ID,t3.Colour from
(
select t0.ID,max(t1.[timestamp]) as maxtime from tblCustomer t0
inner join tblColour t1 on t0.ID=t1.ID
group by t0.ID
) t2
inner join tblColour t3 on t2.ID=t3.ID
and t2.maxtime=t3.[timestamp]
0
 

Expert Comment

by:jat0818
ID: 34175108
nvm,
pretty much what you had, did not see your entire statment.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 34178081
select C.ID, C2.Colour
from tblCustomer C
join(
      select IDCustomer, Colour, rn=row_number() over (partition by IDCustomer order by timestamp desc)
      from tblColour) C2 ON C.ID = C2.IDCustomer and C2.rn=1
0
 
LVL 1

Author Closing Comment

by:PC-Alex
ID: 34178611
Thank you guys; I split points, because angel was faster and brought some background, and cyberkiwi took the time to type the solution.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…

829 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