SQL server - Get newest record of join

After reading Stackoverflow for quite some time, today it's my turn to ask a question!

Table structure

It's related to a query to a SQL server 2005 database. I have the following table structure:

comp开发者_如何学Goonents

• id

downloads

• id
• componentid (FK to components#id)
• downloaddate

imports

• id
• downloadid (FK to downloads#id)
• status

What I want to get

I am trying to get the status of the latest download for each component.

Where I am so far

I have started writing this query, but my join is not working quite well:

SELECT c.id  
FROM components as c  
JOIN (    
  SELECT componentid, MAX(downloaddate) as downloaddate, id  
  FROM downloads  
  GROUP BY componentid, id   
) d ON d.componentid = c.id  
-- JOIN on imports here...

My problem is that the query used for the join is not correct. Because I need to select the id for the join, I added it in the group by clause and I now get multiple records for each componentid, instead of the latest one.

Here is what I get if I execute the query in the join on its own.

componentid downloaddate            id

8882    2011-01-15 06:00:09.773 595862
8882    2011-01-16 06:00:09.847 598422
8882    2011-01-17 06:00:09.940 600971
8951    2010-11-15 03:00:22.550 450221
8951    2010-11-16 03:00:21.730 452431
8951    2010-11-17 03:00:21.920 454668
8961    2011-01-17 02:00:33.077 600532
8961    2011-01-17 02:00:59.070 600536
8983    2008-07-17 11:59:53.780 122
9005    2009-09-01 04:00:38.320 48918
9005    2009-09-02 04:00:36.260 49280

Instead, I need something like this:

componentid downloaddate            id

8882    2011-01-17 06:00:09.940 600971
8951    2010-11-17 03:00:21.920 454668
8961    2011-01-17 02:00:59.070 600536
8983    2008-07-17 11:59:53.780 122
9005    2009-09-02 04:00:36.260 49280

Does anyone know how I should write my join to achieve the expected result?

Thanks a lot in advance

You cannot correlate the JOIN operator. Use APPLY instead:

SELECT c.id , d.downloaddate
FROM components as c  
APPLY (    
  SELECT componentid, MAX(downloaddate) as downloaddate  
  FROM downloads  
  WHERE componentid = c.id
) as d;

Actually you could use a JOIN too (since the inner query is no correlated actually), but don't group by id, just group by componentid:

SELECT c.id , d.downloaddate
FROM components as c  
JOIN (    
  SELECT componentid, MAX(downloaddate) as downloaddate  
  FROM downloads  
  GROUP BY componentid 
) as d ON c.id = d.componentid;

Updated:

SELECT *
FROM components as c  
APPLY (    
  SELECT TOP(1) componentid, downloaddate, id as downloadid
  FROM downloads  
  WHERE componentid = c.id
  ORDER BY downloaddate DESC
) as d
JOIN imports i on i.id = d.downloadid;

You could probably solve this using the ROW_NUMBER function:

SELECT componentid, ROW_NUMBER() OVER (PARTITION BY componentid ORDER BY downloaddate DESC) as rowno
FROM downloads  
WHERE rowno = 1