Linked Questions

Popular Questions

Multiple order_by not ordering by correctly in psql

Asked by At

Attempting to order by two columns in the following query. I want to find the nearest people and then order by the highest ranking.

When I run this query however, it only returns the nearest people and does not applying the second ordering

SELECT person.fullname, person.ranking_score ranking, ST_Distance(person.location, CAST(ST_SetSRID(ST_MakePoint(-0.1642618, 51.58763), 4326) AS geography(GEOMETRY,-1))) AS distance
FROM person
ORDER BY distance ASC, ranking DESC;

returns

 fullname |  ranking   |   distance    
----------+------+------------+-------
 User 1   |    2.62    | 1281.06710415
 User 2   |    5.218   | 1610.4042403
 User 3   |    3.72    | 3005.28625016
 User 4   |    2.82    | 3005.28625016
 User 5   |    6.826   | 3186.54245429

As you can see the correct ordering is applied to the distance, but not to the ranking too.

EDIT:

I expected that the initial ordering of distance would be applied and then the next ordering would be applied to that result set?

 fullname |  ranking   |   distance    
----------+------+------------+-------
 User 5   |    6.826   | 3186.54245429
 User 2   |    5.218   | 1610.4042403
 User 3   |    3.72    | 3005.28625016
 User 4   |    2.82    | 3005.28625016
 User 1   |    2.62    | 1281.06710415

Related Questions