ORDER BY with PostgreSQL

Question

Is there a way to order your table by something that you don't select in the select clause such as:

Table Example

   Column  |    Type   |
-----------+-----------+
language   | character |
percentage | real      |
id         | integer   |

So I want to ideally do something like this

SELECT DISTINCT language FROM Example ORDER BY percentage DESC;

But this obviously doesn't work. Is there a way that I can actually get it to order it by the percentage without actually selecting percentage in the SELECT clause?

This query

SELECT DISTINCT language 
FROM countrylanguage 
ORDER BY percentage DESC;

gives this error message:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 1: ...T DISTINCT language FROM countrylanguage ORDER BY percentage...

SELECT DISTINCT language FROM countrylanguage ORDER BY percentage DESC; ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ...T DISTINCT language FROM countrylanguage ORDER BY percentage... — Josh Valdivieso, Sep 11 '12 at 00:35

Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-09-11T00:46:57.077

2

You don't need to select a column to sort on it.

create table language_pct (
  id integer primary key,
  language varchar(15) not null,
  percentage real not null,
  unique (language)
);

insert into language_pct values
(1, 'English', 15.3),
(2, 'French', 32.108),
(3, 'Russian', 12.88);

select language
from language_pct
order by percentage desc;

French
English
Russian

And if you had two rows for English, each with a different percentage, what would you have the dbms do for select distinct...?

edited Sep 11 '12 at 00:46

answered Sep 11 '12 at 00:39

Mike Sherrill 'Cat Recall'

91,602
17
122
185

Okay yea that works, I was looking at my tables wrong because I have multiple languages in my table that correspond to different countries or regions so that's why I was putting DISTINCT in there but I forgot different regions might contain the same language. Thanks – Josh Valdivieso Sep 11 '12 at 00:47

score 1 · Answer 2 · edited May 23 '17 at 12:22

If you have multiple entries per language, want only one row per language in the result, and still want to sort by a percentage, you may want to GROUP BY language and aggregate the percentage with sum() (or another aggregate function, depending on your actual data):

SELECT language, sum(percentage) AS total_percentage
FROM   countrylanguage
GROUP  BY language
ORDER  BY total_percentage DESC;

If you just want one entry per language with the biggest percentage, you can use DISTINCT ON but to-be-distinct columns have to come first in the ORDER BY clause:

SELECT DISTINCT ON (language)
       language, percentage
FROM   countrylanguage
ORDER  BY language, percentage DESC;

About DISTINCT ON:

Select first row in each GROUP BY group?

To sort by language now, you would have to put that into a subquery:

SELECT * FROM (
   <query from above>
   ) AS sub
ORDER  BY percentage DESC, language; -- language only serves as tiebreaker

Or use a different route with GROUP BY:

SELECT language, max(percentage) AS max_percentage
FROM   countrylanguage
GROUP  BY language
ORDER  BY max_percentage DESC;

Or combine DISTINCT with window functions:

SELECT * FROM (
   SELECT DISTINCT
          language, max(percentage) OVER (PARTITION BY language) AS max_percent
   FROM   countrylanguage
   ) AS sub
ORDER  BY max_percent DESC;

The last one will be slowest in this case.

ORDER BY with PostgreSQL

2 Answers2