SQL Specific Order

Question

Given the following query:

SELECT DISTINCT n.iswinner, i.name 
  FROM nominees n, institutions i
  WHERE n.iid = i.iid and n.filmname = '127 Hours' 
  ORDER BY name

I get the output:

iswinner    name

NULL    academy awards
NULL    baftas
NULL    critics' choice awards
NULL    golden globes
NULL    screen actors guild
NULL    writers guild of america

I am trying to figure out if it is possible to order this output in a more specific manner. The order I am looking for is to list first 'academy awards', then 'golden globes' then anything with a 'guild' in its name, and finally anything else alphabetically. So therefore the output I'm looking for is more along the lines of this:

iswinner    name

NULL    academy awards
NULL    golden globes
NULL    screen actors guild
NULL    writers guild of america
NULL    bafta
NULL    critics' choice awards

Is there a way to do such a thing? I believe I should use something like CASE, but I couldn't seem to figure out the correct syntax for it. Thanks for any help.

Or have another "dictionary" table to make it more flexible and order by its ID — mlt
– mlt, Commented Mar 31, 2013 at 20:27
@joeframbach: PostgreSQL != MySQL and field is MySQL-specific. — mu is too short
– mu is too short, Commented Mar 31, 2013 at 20:44

Michael Bowersox · Accepted Answer · 2013-04-01 15:50:18Z

3

Yes there is a way to do something like this and just like you thought, you can do this with a CASE statement. Something like the following should do the trick:

SELECT
    DISTINCT n.iswinner,
    i.name,
    CASE
        WHEN i.name = 'academy awards' THEN 1
        WHEN i.name = 'golden globes' THEN 2
        WHEN i.name like '%guild%' THEN 3
        ELSE 4
    END AS Order
FROM nominees n, institutions i
WHERE n.iid = i.iid and n.filmname = '127 Hours'
ORDER BY
    Order,
    i.name

So, to give you a little more information on what is being done here. In the ORDER clause, we're ordering by a CASE statement. Basically, based upon what the i.name field is, we are assigning an integer number to order by. academy awards is assigned 1, golden globes is assigned 2, anything that contains 'guild' is assigned 3, and anything else is assigned 4. So we first order by this CASE statement (which gives the specific ordering you want) and then order by the name field which will satisfy your the second requirement of ordering anything else by name (which we previously assigned the value of 4 in the CASE statement for ordering).

I hope this makes sense to you.

Same query using GROUP BY rather than DISTINCT:

SELECT
    n.iswinner,
    i.name
FROM nominees n, institutions i
WHERE n.iid = i.iid and n.filmname = '127 Hours'
GROUP BY 
    n.iswinner,
    i.isname
ORDER BY
    CASE
        WHEN i.name = 'academy awards' THEN 1
        WHEN i.name = 'golden globes' THEN 2
        WHEN i.name like '%guild%' THEN 3
        ELSE 4
    END,
    i.name

edited Apr 1, 2013 at 15:50

answered Mar 31, 2013 at 20:28

Michael Bowersox

1,48111 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Night Train Over a year ago

The explanation makes complete sense thank you. However, when attempting to implement this, I get the following error: "for SELECT DISTINCT, ORDER BY expressions must appear in select list"

Michael Bowersox Over a year ago

Seems like that error is saying that you have to include all of the columns in the ORDER BY in the SELECT query when using the DISTINCT keyword. Try adding the CASE statement as a column in the SELECT clause. I will update the answer to reflect this.

Night Train Over a year ago

Hmm, that certainly does seem to fix the issue, but does add an extra column to my table, is there anyway to avoid this? Other than simply making it a subquery and selecting from that.

Michael Bowersox Over a year ago

Instead of using DISTINCT, you can try using the GROUP BY clause. Let me write the same query with a GROUP BY instead.

Night Train Over a year ago

That makes a great deal of sense, and gives me the results I'm looking for. Thank you for the help and detailed explanations.

|

Andomar · Accepted Answer · 2013-03-31 20:24:07Z

3

order by
        case 
        when name = 'academy awards' then 1
        when name = 'golden globes' then 2
        when name like  '%guild%' then 3
        else 4
        end
,       name

answered Mar 31, 2013 at 20:24

Andomar

239k55 gold badges387 silver badges412 bronze badges

1 Comment

Night Train Over a year ago

For the third and 4th case, will those be listed in alphabetical order themselves as well? If so this seems to be exactly what I'm looking for.

Clodoaldo Neto · Accepted Answer · 2013-04-02 09:25:28Z

1

No need for a case clause. SQL Fiddle

select *
from (
    select distinct n.iswinner, i.name
    from nominees n, institutions i
    where n.iid = i.iid and n.filmname = '127 Hours'
) s
order by
    name != 'academy awards',
    name != 'golden globes',
    name not like '%guild%',
    name

false orders before true

edited Apr 2, 2013 at 9:25

answered Mar 31, 2013 at 23:07

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

SQL Specific Order

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related