CODEWITHSUNDEEP
postgresql
Mkay
Mkay

Reputation: 123

How to order the table by the contents of a column

For school I have to write a query that returns the names of players and the average of their fines (if they had fines otherwise the column should contain 'geen boetes'). The players with 'geen boetes' must come first then I have to order by name, average. The code I have right now is the following:

select 
    s.naam, 
    case 
        when avg(bedrag) is null then 'geen boetes' 
        else cast(round(avg(bedrag), 2) as varchar(8)) 
    end as gemiddeld
from spelers s 
left outer join boetes b using (spelersnr)
group by s.spelersnr, s.naam
order by 
    case 
        when gemiddeld like 'geen boetes' then 1 
        else s.naam, gemiddeld 
    end

Everything works like it's supposed to except I can't get the order by to work properly.

Upvotes: 1

Views: 51

Answers (2)

GMB
GMB

Reputation: 222542

The players with 'geen boetes' must come first then I have to order by name, average

Consider:

order by 
    case when gemiddeld = 'geen boetes' then 0 else 1 end, 
    s.naam, 
    gemiddeld

This creates three levels of ordering, one for each of your sorting criteria.

As far as concerns Postgres supports column aliases in the order by clause. However if the expression is not supported, then you can use the following:

order by 
    case when avg(bedrag) is null then 0 else 1 end, 
    s.naam, 
    gemiddeld

Upvotes: 1

forpas
forpas

Reputation: 164139

If you want the players with 'geen boetes' must come first then the CASE statement must return a lower value for this case than the ELSE part.
You can do it without the CASE statement if you set a boolean expression in the ORDER BY clause:

order by 
    avg(bedrag) is not null, 
    s.naam, 
    gemiddeld

The expression avg(bedrag) is not null will return TRUE or FALSE and since TRUE > FALSE all the null values will be at the top.

Upvotes: 0

Related Questions