CODEWITHSUNDEEP
sqlsortingsql-order-by
Nolween Lopez
Nolween Lopez

Reputation: 53

SQL Order by group of specific values

I tried to found the solution, I might have done wrong researches, that's why I need your help :(

There is 6 different categorie, with different values. I want to select all of them, but ordered in 2 differents groups : the first would contain all between 1 and 3, ordered by another value. Always in the same request, I want to display category between 4 and 6, ordered by another value.

The better way is to show you before and after, what I would like :

BEFORE

|Category | name     |
|    1    | Barney   |
|    6    | Ted      |
|    6    | Anita    |
|    3    | Jessica  |
|    2    | Marshall |
|    3    | Lily     |
|    4    | Robin    |
|    2    | Bryan    |
|    5    | Oliver   |

AFTER

|Category | name     | ----- Alphabetic sort
|    1    | Barney   |
|    2    | Bryan    |
|    3    | Jessica  |
|    3    | Lily     |
|    2    | Marshall |
---------------------------Imaginary line which seperate 2 groups : category 1 2 3 and 4 5 6
|    6    | Anita    |
|    5    | Oliver   |
|    4    | Robin    |
|    6    | Ted      |

I hope you understood what I meaned !

Thank you for your help !

Upvotes: 0

Views: 997

Answers (3)

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

If you are using MySQL or PostgreSQL you can easily get this by using ORDER BY category>3, name, assuming there are only these two possible groups.

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

ORDER BY CASE  
            WHEN category IN (1, 2, 3) THEN 1
            WHEN category IN (4, 5, 6) THEN 2
            ELSE 3
         END,
         name

The query uses a CASE expression in order to group together category subsets: subset 1, 2, 3 is assigned a value of 1 and hence has the greatest priority. Subset 4, 5, 6 is assigned a value of 2, whereas the rest of categories get the lowest priority, i.e. the value of 3.

Upvotes: 5

Andrei Duca
Andrei Duca

Reputation: 392

In your SELECT statement try:

... ORDER BY IF(Category<4,0,1) ASC, name ASC

Upvotes: 1

Related Questions