-1

I'm stuck writing a query for the following scenario: I have a table of things, they have a metric called number in the examples below. I have another table called offers which is a list of offers made on a particular thing. I'm looking for the highest offer on each thing that is less than or equal to the number on a thing.

I can't seem to come up with the right JOIN and MAX() to satisfy the query, and end up running in circles.

things table:

id number description
1 5 Thing 1
2 10 Thing 2
3 8 Thing 3

offers table:

thing_id offer description
1 1 thing 1 offer 1
1 3 thing 1 offer 2
1 12 thing 1 offer 3
2 9 thing 2 offer 1
3 1 thing 3 offer 1
3 8 thing 3 offer 2

Desired output:

thing_id number offered thing_description offer_description
1 5 3 Thing 1 thing 1 offer 2
2 10 9 Thing 2 thing 2 offer 1
3 8 8 Thing 3 thing 3 offer 2
RDD Brian
  • 1
  • 1
  • 1

1 Answers1

1

You could try along

select
  x.thing_id,
  x.numbr,
  x.offered,
  x.thing_description,
  o.description as offer_description
from (
  select
    t.id as thing_id,
    t.numbr,
    max(o.offer) as offered,
    t.description as thing_description
  from Things t
  join Offers o
    on t.id = o.thing_id
    and t.numbr >= o.offer
  group by t.id, t.numbr, t.description
  ) x
join Offers o
  on x.thing_id = o.thing_id
  and x.offered = o.offer
;

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.

Abecee
  • 610
  • 1
  • 4
  • 8