Error "column does not exist" in a SELECT with JOIN and GROUP BY query

Question

I'm using PostgreSQL 9.1 with a Ruby on Rails application.

I'm trying to list the last version of each "charge" (in my history table : hist_version_charges) belonging to the same project id (proj_sous_projet_id = 2).

This makes me use the max() aggregate function and apply the result to a JOIN function on the same table as PostgreSQL does not authorize to use the columns in the SELECT clause if they do not appears in the GROUP BY clause, ALTHOUGH using a max() mean obviously I'm interested to the row containing the max values!

This is my query :

SELECT h_v_charges.*, 
 max(last_v.version) as lv 
FROM hist_versions_charges h_v_charges 
 JOIN hist_versions_charges last_v 
 ON h_v_charges.version = lv 
 AND h_v_charges.proj_charge_id = last_v.proj_charge_id 
GROUP BY last_v.proj_sous_projet_id, 
 last_v.proj_charge_id 
HAVING last_v.proj_sous_projet_id = 2 
ORDER BY h_v_charges.proj_charge_id ASC;

The error message I got :

ERROR: column "lv" does not exist
LINE 1: ..._versions_charges last_v ON h_v_charges.version = lv AND h_v...
 ^
********** Error **********
ERROR: column "lv" does not exist
SQL state: 42703
Character: 147

I also tried with "last_v.lv" but the error remains the same.

If anybody got an idea about what's wrong, she is more than welcome.

=== UPDATE ===

According to * a_horse_with_no_name * and Colin 't Hart answers, I finally ended up with the following query :

SELECT *
FROM (
 SELECT *, max(version) OVER (PARTITION BY proj_charge_id) AS lv
 FROM hist_versions_charges
 WHERE proj_sous_projet_id = 2) AS hv
WHERE hv.lv = hv.version
ORDER BY hv.proj_charge_id ASC;

It is slightly quicker with a single ORDER BY.

I tried as well the query with a WITH clause. Though "nicer", it creates additional processing charge. As I know I will not re-used in the future the sub-query twice or more in the same main query, I'm fine with using a simple sub-query.

Thanks anyway to *a_horse_with_no_name* and Colin 't Hart. I learned many things !

• user1822: "does not authorize to use the columns in the SELECT clause if they do not appears in the GROUP BY clause" so does every other sensible DBMS.
• Douglas: Yes, but, as explained here : rpbouman.blogspot.se/2007/05/debunking-group-by-myths.html , the 2003 SQL standard defines it should be possible to use functionally dependent column.
• user1822: which Postgres 9.1 (and later) does: postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY
• ypercubeᵀᴹ: @a_horse_with_no_name You are right. but the above query is not valid, even by the 2003 standard because it uses the alias (defined in SELECT list) in the FROM clause (actually the ON part but that's still in the FROM clause)!
• Douglas: @a_horse_with_no_name : Postgresql is interpreting the standard "functionnaly dependent" by linking the term to a primary key. However, I'm talking about "common-sense". If you find a max() value, postgresql already knows on wich row this max() value is stored. So postgresql should be able to access any columns regardless of the GROUP BY clause with such aggregate function like max(). This is just a question of logic and common sense. Any way my querry was incorrect, and many thanks for pointing me the parenthesis error.

Answer 1

You probably want something like this:

SELECT h_v_charges.*, 
 last_v.last_version
FROM hist_versions_charges h_v_charges 
 JOIN (select proj_charge_id, 
 max(version) as last_version
 from hist_versions_charges 
 where proj_sous_projet_id = 2 
 group by proj_charge_id
 ) last_v 
 ON h_v_charges.version = last_v.last_version
 AND h_v_charges.proj_charge_id = last_v.proj_charge_id 
ORDER BY h_v_charges.proj_charge_id ASC;

A possibly (because no join is required) faster solution would be:

select *
from (
 select hvc.*, 
 row_number() over (partition by proj_charge_id order by version desc) as rn
 from hist_versions_charges as hvc
 where proj_sous_projet_id = 2 
) as hv
where rn = 1
order by hv.proj_charge_id ASC;

As Colin has pointed out, this can also be written as:

with hv as (
 select hvc.*, 
 row_number() over (partition by proj_charge_id order by version desc) as rn
 from hist_versions_charges as hvc
 where proj_sous_projet_id = 2 
) 
select *
from hv
where rn = 1
order by hv.proj_charge_id ASC;

• user1822: @Douglas: using window functions might give you a better performance. You can compare the execution plans to find out which one is more efficient.
• Douglas: Wow ! Window functions. This is a brand new world for me. It seems prowerfull. Great thanks, I will study this deeply. Just a question what "hv" is related to in your querry ? I can see "hvc" which may be linked to hist_versions_charges with a missing AS clause, but is the hv a function or just a typo mistake ?
• user1822: @Douglas: hv is an alias for the derived table (aka "sub-query"). And you are right, I forgot the alias inside the derived table.
• Colin 't Hart: You can make the second solution even nicer by using a with clause. The big advantage is if you decide in future you need to use the subquery twice you can reference it by name, whereas with the subquery approach you have to repeat the subquery.
• user1822: @Colin'tHart: I know ;) I just didn't want to introduce too many new things (And I wanted to somehow stay close to the original query).