CODEWITHSUNDEEP
sql
S.L. Chandrasinghe
S.L. Chandrasinghe

Reputation: 11

SQL order by statement

I want to order some student details according to their distance to school. But within (80-90)km order first. remaining students will be displayed then. (select student_name, distance from Students ...........) ex :

Can somebody help me on this.

Upvotes: 1

Views: 70

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

In order to get the 80-90 km distances first, use a CASE construct:

ORDER BY
  CASE WHEN distance BETWEEN 80 AND 90 THEN 0 ELSE 1 END,
  distance;

This will get you this order:

Geetha    82.5 km
Nimal     86.1 km
kamal     88.2 km
Rani      23.1 km
Perera    92.3 km
Shashi   102.4 km

First 80-90 km beginning with 82.5 ending with 88.2, then the other distances beginning with 23.1 ending with 102.4.

Upvotes: 3

Joop Eggen
Joop Eggen

Reputation: 109557

SELECT ..., ROUND(distance / 10, 0) AS lakhm, ...
...
ORDER by lakhm

This assumes that your SQL version has a ROUND working on expressions. The formal standard requires a column name only I believe. I doubt it works, but ROUND(distance, -1) should have an equivalent effect.

Upvotes: 0

Related Questions