Group by calculated field within multiple inner joins / subselects

I’m trying to find stories that are the closest distance from the user. However, only chapters in our stories have a place, so I need to get those items and then get the closest chapter from that list, then sort the stories via that distance that has been retrieved. The hardcoded numbers here are just for testing/example and would be dynamically replaced with the user’s location at query time.

I’ve created a function that gets me pretty close, but it has multiple entries for each chapter instead of just one for each story using the minimum, distance value from the chapters.

select
    story.id,
    story.name,
    earth_distance(ll_to_earth(place.longitude, place.latitude), ll_to_earth(153.03563, -27.38223)) as distance
from story
inner join chapter on chapter.story_id = story.id
inner join place on chapter.place_id = place.id
order by distance

I have a query that gives me the minimum chapter distance out of all the chapters:

 select MIN (earth_distance(ll_to_earth(place.longitude, place.latitude), ll_to_earth(153.03563, -27.38223))) as distance 
    from chapter 
    left join place on chapter.place_id = place.id

This works as expected.

I played with a subselect version of this that says distance isn’t defined:

select 
  story.id,
  distance
from story
where 
  distance = (
    select MIN (earth_distance(ll_to_earth(place.longitude, place.latitude), ll_to_earth(153.03563, -27.38223))) as distance 
    from chapter, place
    where chapter.story_id = story.id
    and chapter.place_id = place.id
    
  );

Is there a way to combine or use these together to just get me each story sorted by their distance?

Answer

You can try this :

select
    story.id,
    story.name,
    min(earth_distance(ll_to_earth(place.longitude, place.latitude), ll_to_earth(153.03563, -27.38223))) as distance_min
from story
inner join chapter on chapter.story_id = story.id
inner join place on chapter.place_id = place.id
group by story.id, story.name
order by distance_min
limit 1

Advertisement

Answer