PostgreSQL Order By – How to Handle Ordering in Subqueries

order-bypostgresql

This is my query:

select locid,tlid,locdescription from (

  select locid, tlid, locdescription
  from 
  trip_log left join trip_log_det on tlid=tldtripid 
  left join locations on tldlocation=locid
  where tlid=50 order by tldid 

) as foo group by locid,tlid,locdescription

The point of using a subquery is to get data back ordered as per the internal query, so sorted by tldid. I do not need tldid in the data as it is a serial column and it would nullify the group by statement.

Is there a way to tell the subquery to maintain the ordering of the internal query?

Best Answer

You don't need a subquery at all. But you have to decide what ordering you want.

After the group by there is no tldid values any more. And every group will be made by collapsing many rows (with different tldid values) into one row. Which of them should be used for the ordering? Is it the maximum, the mimimum or some other?

You can write for example:

select locid, tlid, locdescription
from 
  trip_log left join trip_log_det on tlid = tldtripid 
  left join locations on tldlocation = locid
where tlid = 50 
group by locid, tlid, locdescription
order by min(tldid) ;

In Postgres, you could also use the non-standard DISTINCT ON syntax to achieve the same result. This is more flexible, as you can have the tldid and other (non-grouped) columns in the select list as well:

select distinct on (locid, tlid, locdescription)
    locid, tlid, locdescription
from 
  trip_log left join trip_log_det on tlid = tldtripid 
  left join locations on tldlocation = locid
where tlid = 50 
order by locid, tlid, locdescription,
         tldid asc ;
Related Question