114

Is it possible to order by multiple rows?

I want my users to be sorted by last_activity, but at the same time, I want the users with pictures to appear before the ones without

Something like this:

SELECT some_cols
FROM `prefix_users`
WHERE (some conditions)
ORDER BY last_activity, pic_set DESC;
cursorrux
  • 1,312
  • 3
  • 8
  • 19
Alexander
  • 2,323
  • 4
  • 22
  • 17

3 Answers3

162
SELECT some_cols
FROM prefix_users
WHERE (some conditions)
ORDER BY pic_set DESC, last_activity;

Note that we can place ASC or DESC after each column (like above does for pic_set), or leave it to default (like above's last_activity-column).

Top-Master
  • 5,262
  • 5
  • 23
  • 45
ihorko
  • 6,509
  • 25
  • 75
  • 112
17

Sort by picture and then by activity:

SELECT some_cols
FROM `prefix_users`
WHERE (some conditions)
ORDER BY pic_set, last_activity DESC;
eumiro
  • 194,053
  • 32
  • 286
  • 259
  • 2
    i dont understand, where does the DESC go?? behind every item? – Alexander Feb 02 '11 at 19:38
  • 2
    Yes. You probably want to order the `last_activity` from newest to oldest. And if you explain how the pictures are defined (what the `pic_set` stands for), I could tell you how to sort this attribute too. – eumiro Feb 03 '11 at 08:02
  • 5
    Alexander, I believe you can do `ORDER BY pic_set DESC, last_activity DESC` or `ORDER BY pic_set DESC, last_activity ASC` so sort direction for each column, default sort direction in mysql (with default configuration) is ASC. – ZurabWeb Jan 30 '14 at 20:29
6
SELECT id, user_id, video_name
FROM sa_created_videos
ORDER BY LENGTH(id) ASC, LENGTH(user_id) DESC
Anton Shurashov
  • 1,670
  • 1
  • 20
  • 33
Pankaj Yadav
  • 163
  • 1
  • 7