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

Question

Table Structure is:

create table fruit (
  id int identity(1,1),
  name varchar(max)
)

create table fruit_allocation (
  id int identity(1,1),
  fruit_id int references fruit(id),
  customer_id int references store(id),
  amount float,
)

create table measurement (
  fruit_allocation_id int references fruit_allocation(id),
  measurement_date datetime,
  measurement float,
)

Each fruit can be allocated to more than one customer creating a fruit_allocation record. Each fruit_allocation record can have multiple measurements.

I want to select the latest measurement for each fruit_allocation given a fruit id

So far I have the following:

select * 
  from measurement 
 where fruit_allocation_id in (select id 
                                 from fruit_allocation 
                                where fruit_id = 10)

This returns all measurements for that fruit, I want to just return 1 measurement per fruit_allocation.

score 12 · Accepted Answer · edited Feb 22 '11 at 19:06

12

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

edited Feb 22 '11 at 19:06

Don Kirkby

53,582
27
205
286

answered Feb 22 '11 at 01:58

RichardTheKiwi

105,798
26
196
262

You might find this discussion of APPLY vs. JOIN interesting: http://stackoverflow.com/q/1139160/4794 – Don Kirkby Feb 22 '11 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 – RichardTheKiwi Feb 22 '11 at 19:22
There are more creative ways of using a join to solve this problem. (See my answer: http://stackoverflow.com/questions/5073089/select-top-1-from-a-table-for-each-id-in-another-table/5073172#5073172 ) However, it's interesting to learn about APPLY, and it sounds like it might be more efficient in this case. – Don Kirkby Feb 22 '11 at 20:18
BTW, BOL? Is it Book O' Learnin'? – Don Kirkby Feb 22 '11 at 20:21
1

@Don - abbreviation for http://msdn.microsoft.com/en-us/library/ms130214.aspx **B** ooks **O** n **L** ine – RichardTheKiwi Feb 22 '11 at 20:24

score 1 · Answer 2 · answered Feb 22 '11 at 02:07

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

score 0 · Answer 3 · answered Oct 02 '14 at 00:35

0

select * from measurement m where measurement_date = (select top 1 measurement_date from measurement m1 where m1.fruit_allocation_id = m.fruit_allocation_id order by measurement_date desc) And fruit_allocation_id in (select id from fruit_allocation where fruit_id = 3)

answered Oct 02 '14 at 00:35

yen

1

but it seems cross apply not so heavy a and can get data from [fruit_allocation] ... – yen Oct 02 '14 at 00:37

Don Kirkby · Answer 4 · 2011-02-22T19:04:27.957

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

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

4 Answers4