CODEWITHSUNDEEP
postgresql
Kwahn
Kwahn

Reputation: 476

Why can't I use an equality in order by with select distincts?

Let's say I have 

select distinct id, flavor from bageltown order by id, flavor

This works.

But if I say 

select distinct id, flavor from bageltown order by id, flavor='lox' desc, flavor

I get an error that an element's not present in the distinct clause.

This is, of course, fixed by editing the query to read as such:

select distinct id, flavor='lox', flavor from bageltown order by id, flavor='lox' desc, flavor

But I'm wondering - why is adding the boolean column to my output necessary?

Upvotes: 1

Views: 26

Answers (1)

George S
George S

Reputation: 2151

The purpose of DISTINCT (rather than DISTINCT ON) is to remove duplicate rows in the resulting table. This may not be a concern in your particular case (since you've got id in the results table), but if you sort by flavor='lox' and flavor='lox' isn't a result column, there's no guarantee that there aren't some rows represented that (if it weren't for duplicates being removed) had both flavor='lox' and flavor<>'lox', and it's not clear how those should be sorted. If you treat flavor='lox' and 'flavor<>'lox' as separate rows, then you're back to having duplicate rows in the table.

Upvotes: 2

Related Questions