postgres column alias problem
In PostgreSQL you can not use expression with an alias in order by. Only plain aliases work there. Your query should look like this:
select distinct l2.*, l.user_id as l_user_id, l.geopoint_id as l_geopoint_id from locations l left join locations l2 on l.geopoint_id = l2.geopoint_id where l.user_id = 8 order by l2.geopoint_id, l.user_id = l2.user_id desc;
I assume you mean that l2.user_id=l.user_id
ought to go first.
This is relevant message on PostgreSQL-general mailing list. The following is in the documentation of ORDER BY
clause:
Each expression can be the name or ordinal number of an output column (SELECT list item), or it can be an arbitrary expression formed from input-column values.
So no aliases when expression used.
I ran into this same problem using functions from fuzzystrmatch - particularly the levenshtein function. I needed to both sort by the string distance, and filter results by the string distance. I was originally trying:
SELECT thing.*, levenshtein(thing.name, '%s') AS dist FROM thing WHERE dist < character_length(thing.name)/2 ORDER BY dist
But, of course, I got the error "column"dist" does not exist" from the WHERE clause. I tried this and it worked:
SELECT thing.*, (levenshtein(thing.name, '%s')) AS dist FROM thing ORDER BY dist
But I needed to have that qualification in the WHERE clause. Someone else in this question said that the WHERE clause is evaluated before ORDER BY, thus the column was non-existent when it evaluated the WHERE clause. Going by that advice, I figured out that a nested SELECT statement does the trick:
SELECT * FROM (SELECT thing.*, (levenshtein(thing.name, '%s')) AS dist FROM thing ORDER BY dist) items WHERE dist < (character_length(items.name)/2)
Note that the "items" table alias is required and the dist column alias is accessible in the outer SELECT because it's unique in the statement. It's a little bit funky and I'm surprised that it has to be this way in PG - but it doesn't seem to take a performance hit so I'm satisfied.
You have:
order by l2.geopoint_id, l_user_id = l2.user_id desc
in your query. That's illegal syntax. Remove the = l2.user_id
part (move it to where
if that's one of the join conditions) and it should work.
Update Below select (with = l2.user_id
removed) should work just fine. I've tested it (with different table / column names, obviously) on Postgres 8.3
select distinct l2.*, l.user_id as l_user_id, l.geopoint_id as l_geopoint_id from locations l left join locations l2 on l.geopoint_id = l2.geopoint_id where l.user_id = 8 order by l2.geopoint_id, l_user_id desc