Special Join scenario

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)
``````

Any idea welcome
LVL 1
Who is Participating?

Commented:
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

Billing EngineerCommented:
http://www.experts-exchange.com/A_3203.html
0

Commented:
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

Commented:
nvm,
0

Author Commented:
Thank you guys; I split points, because angel was faster and brought some background, and cyberkiwi took the time to type the solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.