Select Top 1 From a Table For Each Id in another Table?

You can CROSS APPLY select a. *, m. * from fruit_allocation a cross apply ( select top 1 * from measurement m where m.

Fruit_allocation_id = a. Id order by m. Measurement_date desc ) m where a.

Fruit_id = 10.

You might find this discussion of APPLY vs. JOIN interesting: stackoverflow. Com/q/1139160/4794 – Don Kirkby Feb 22 at 19:07 @Don The "vs" only applies if the JOIN will work at all. In this case, a JOIN cannot work out a correlated TOP 1.

But thanks for linking to BOL and for a good read – Richard aka cyberkiwi Feb 22 at 19:22 There are more creative ways of using a join to solve this problem. (See my answer: stackoverflow. Com/questions/5073089/… ) However, it's interesting to learn about APPLY, and it sounds like it might be more efficient in this case.

– Don Kirkby Feb 22 at 20:18 BTW, BOL? Is it Book O' Learnin'? – Don Kirkby Feb 22 at 20:21 @Don - abbreviation for msdn.microsoft.Com/en-us/library/ms130214.

Aspx B ooks O n L ine – Richard aka cyberkiwi Feb 22 at 20:24.

Assuming you are using SQL Server 2005+ With RankedMeasurements As ( Select M. Fruit_allocation_id , M. Measurement_date , M.

Measurement , Row_Number() Over ( Partition By M. Fruit_allocation_id Order By M. Measurement_date Desc ) As Rnk From measurement As M Where Exists ( Select 1 From fruit_allocation As FA1 Where FA1.Id = M.

Fruit_allocation_id And FA1. Fruit_id = 10 ) ) Select RM. Fruit_allocation_id , RM.

Measurement_date , RM. Measurement From RankedMeasurements As RM Where Rnk = 1.

Create a subquery to find the latest measurement for each allocation, then join to that subquery as if it were a real table. Select * from measurement meas join ( SELECT fruit_allocation_id, MAX(measurement_date) as max_date FROM measurement meas2 JOIN fruit_allocation alloc ON alloc. Id = meas2.

Fruit_allocation_id where fruit_id = 10 ) max_meas on meas. Fruit_allocation_id = max_mes. Fruit_allocation_id and meas.

Measurement_date = max_meas. Max_date.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.