Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 !

share|improve this question
1  
"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. –  a_horse_with_no_name Apr 23 '13 at 12:51
    
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. –  Douglas Apr 23 '13 at 14:37
    
which Postgres 9.1 (and later) does: postgresql.org/docs/current/static/sql-select.html#SQL-GROUPBY –  a_horse_with_no_name Apr 23 '13 at 14:46
1  
@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)! –  ypercube Apr 23 '13 at 16:21
    
@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. –  Douglas Apr 24 '13 at 8:50
show 2 more comments

1 Answer

up vote 6 down vote accepted

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;
share|improve this answer
    
Great ! That's it ! Thanks –  Douglas Apr 23 '13 at 14:33
1  
@Douglas: using window functions might give you a better performance. You can compare the execution plans to find out which one is more efficient. –  a_horse_with_no_name Apr 23 '13 at 14:47
    
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 ? –  Douglas Apr 24 '13 at 9:05
1  
@Douglas: hv is an alias for the derived table (aka "sub-query"). And you are right, I forgot the alias inside the derived table. –  a_horse_with_no_name Apr 24 '13 at 9:05
1  
@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). –  a_horse_with_no_name Apr 24 '13 at 10:29
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.