CODEWITHSUNDEEP
sqlpostgresqlsql-order-bydistinct
user3309314
user3309314

Reputation: 2543

PostgreSQL, distinct, order by and parentheses

I have the following request:

select distinct m1.firstname, m1.surname 
  from cd.members as m1 
  join cd.members as m2 on m2.recommendedby = m1.memid 
  order by m1.surname, m1.firstname;

and it works fine. But this one: 

select distinct m1.firstname, m1.surname 
  from cd.members as m1 
  join cd.members as m2 on m2.recommendedby = m1.memid 
  order by (m1.surname, m1.firstname);

gives me the error:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

I can't understand when I have to use parentheses and when not.

Upvotes: 1

Views: 283

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Postgres has the concept of tuples or composite types. These are scalar values that appear together -- much like a record or struct in many programming languages.

You can use tuples for expressions, such as:

where (m1.surname, m1.firstname) in ( ('a', 'b'), ('x', 'y') )

The problem with your order by expression is that the tuple is not in the select. You could solve this by using parentheses there too:

select distinct (m1.firstname, m1.surname)
from cd.members m1 join
     cd.members m2
     on m2.recommendedby = m1.memid 
order by (m1.surname, m1.firstname);

But I would stick with the parentheses-less version, which is standard SQL and works in all databases.

Upvotes: 3

Related Questions