9.5. Sorting

So far, we’ve only looked at rows of data in the order of the query is returning to us. What if we want to see the rows in a certain sorting order? We use the ORDER BY command to sort them by some other criteria.

For example, to see the bike trips in the order of the duration in seconds:

%%sql

SELECT
  member_type, start_date, duration
FROM
  trip_data
ORDER BY
  duration
LIMIT
  10
 * sqlite:///bikeshare.db
Done.
member_type start_date duration
Member 2011-01-08 12:28:59.000000 60
Member 2011-01-10 19:46:08.000000 60
Member 2011-01-17 03:59:57.000000 60
Member 2011-01-26 12:03:06.000000 60
Member 2011-01-29 14:01:28.000000 60
Member 2011-02-04 22:01:04.000000 60
Member 2011-02-23 09:23:36.000000 60
Member 2011-02-25 14:02:05.000000 60
Member 2011-02-27 14:03:44.000000 60
Member 2011-03-21 21:47:30.000000 60

Well, it turns out by default the sorting order is ascending. To sort the rows in descending order, add the keyword DESC.

%%sql

SELECT
  member_type, start_date, duration
FROM
  trip_data
ORDER BY
  duration DESC
LIMIT
  10
 * sqlite:///bikeshare.db
Done.
member_type start_date duration
Member 2011-06-09 19:18:26.000000 86355
Casual 2011-04-29 10:37:47.000000 86337
Member 2011-11-16 18:32:05.000000 86336
Casual 2011-05-14 10:12:06.000000 86187
Member 2011-07-07 11:34:45.000000 85679
Casual 2011-06-05 23:40:33.000000 85674
Casual 2011-06-05 23:40:53.000000 85666
Casual 2011-10-08 14:22:29.000000 85657
Member 2011-01-28 10:15:55.000000 85518
Casual 2011-07-02 14:38:06.000000 85505

Of course, we can mix WHERE and ORDER BY, to get only the bike trips from Member type of Casual in the order of the duration.

%%sql

SELECT
  member_type, start_date, duration
FROM
  trip_data
WHERE
  member_type = "Casual"
ORDER BY
  duration
LIMIT
  10
 * sqlite:///bikeshare.db
Done.
member_type start_date duration
Casual 2011-12-28 09:47:29.000000 61
Casual 2011-03-05 15:48:04.000000 62
Casual 2011-07-29 04:08:15.000000 62
Casual 2011-08-29 12:39:15.000000 64
Casual 2011-09-01 10:40:15.000000 65
Casual 2011-10-28 02:30:20.000000 70
Casual 2011-12-18 16:15:28.000000 71
Casual 2011-09-03 23:51:53.000000 72
Casual 2011-02-14 12:52:52.000000 73
Casual 2011-08-29 13:58:16.000000 73

9.5.1. Exercise 2

Get the start and end station IDs for bike trips that are longer 60 minutes or longer, in the order of largest number of seconds first and display the top 40 results.

duration start_station end_station
86355 31232 31611
86337 31221 31221
86336 31400 31206
86187 31705 31705
85679 31104 31107
85674 31617 31617
85666 31617 31617
85657 31238 31230
85518 31200 31226
85505 31200 31200
85504 31200 31200
85484 31102 31223
85425 31605 31605
85322 31303 31303
85318 31200 31213
85311 31232 31400
85213 31107 31107
85194 31223 31010
85178 31108 31108
85168 31217 31217
85131 31238 31217
85102 31238 31217
85072 31613 31607
85020 31600 31201
84988 31613 31607
84962 31229 31213
84958 31228 31227
84849 31205 31205
84841 31619 31617
84812 31108 31262
84614 31302 31302
84589 31607 31605
84417 31801 31801
84315 31603 31602
84306 31223 31223
84298 31109 31109
84249 31108 31108
84219 31108 31108
84204 31014 31014
84190 31112 31018

Lesson Feedback

    During this lesson I was primarily in my...
  • Comfort Zone
  • Learning Zone
  • Panic Zone
    Completing this lesson took...
  • Very little time
  • A reasonable amount of time
  • More time than is reasonable
    Based on my own interests and needs, the things taught in this lesson...
  • Don't seem worth learning
  • May be worth learning
  • Are definitely worth learning
    For me to master the things taught in this lesson feels...
  • Definitely within reach
  • Within reach if I try my hardest
  • Out of reach no matter how hard I try
Next Section - 9.6. Aggregation or Group By