CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
John Mike
John Mike

Reputation: 2095

Multiple order_by not ordering by correctly in psql

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

Upvotes: 0

Views: 701

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

For your sample data, you want to order by the ranking first:

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

I suspect that in your real problem you would have either a condition limiting the distance or want to fetch a certain number of rows of the nearest people The latter case requires a subquery.

Upvotes: 1

Related Questions