up vote 3 down vote favorite

I am using PostgreSQL version 9.1 and looking at the Postgres docs, I know it is possible to do the following:

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

and this works for my queries.

Now I need to be able to specify an alias instead of a column name in the OVER (ORDER BY ...) ?

I have tried this:

EDIT: I previously forgot to add the rank() to this query:

SELECT salary, <complex expression> as result, rank() OVER (ORDER BY result) FROM empsalary;

and I am getting an error saying that column "result" does not exist.

Is it possible to specify an alias instead of a column name here? Am I missing some special syntax?

EDIT:

I am using Hibernate, with some native SQL for the window function. The full SQL that is generated and executed is below:

select 
       rank() OVER (ORDER BY deltahdlcOverruns DESC) as rank, 

       this_.deviceNo as y1_, 

       (SELECT _dev_.hdlcOverruns FROM abstractperformancestatistic _abs_ 
        INNER JOIN enddevicestatistic _dev_ ON _dev_.id = _abs_.id 
        INNER JOIN linkstatistic _link_ ON _link_.id = _dev_.linkStatistic_id 
        INNER JOIN iptstatistic _ipt_ ON _ipt_.id = _link_.iptStat_id 
        WHERE this_.deviceNo=_dev_.deviceNo AND _abs_.dateTime <= '3910-06-07 00:00:00.0'
        ORDER BY _abs_.dateTime DESC LIMIT 1
       ) 
       - 
       (SELECT _dev_.hdlcOverruns FROM abstractperformancestatistic _abs_ 
        INNER JOIN enddevicestatistic _dev_ ON _dev_.id = _abs_.id 
        INNER JOIN linkstatistic _link_ ON _link_.id = _dev_.linkStatistic_id 
        INNER JOIN iptstatistic _ipt_ ON _ipt_.id = _link_.iptStat_id 
        WHERE this_.deviceNo=_dev_.deviceNo AND _abs_.dateTime >= '3870-06-01 00:00:00.0' 
        ORDER BY _abs_.dateTime LIMIT 1
       ) 
       AS deltahdlcOverruns from EndDeviceStatistic this_ 

       inner join AbstractPerformanceStatistic this_1_ on this_.id=this_1_.id 
       inner join AbstractEntity this_2_ on this_.id=this_2_.id 
       left outer join RawEndDeviceStatistic this_3_ on this_.id=this_3_.id 
       left outer join LinkStatistic l2_ on this_.linkStatistic_id=l2_.id 
       left outer join AbstractPerformanceStatistic l2_1_ on l2_.id=l2_1_.id 
       left outer join AbstractEntity l2_2_ on l2_.id=l2_2_.id 
       left outer join RawLinkStatistic l2_3_ on l2_.id=l2_3_.id 
       left outer join IPTStatistic i1_ on l2_.iptStat_id=i1_.id 
       left outer join AbstractPerformanceStatistic i1_1_ on i1_.id=i1_1_.id 
       left outer join AbstractEntity i1_2_ on i1_.id=i1_2_.id 
       left outer join RawIPTStatistic i1_3_ on i1_.id=i1_3_.id 

       where this_1_.dateTime between ? and ? 

       group by this_.deviceNo limit ?
share|improve this question
75% accept rate
I took the liberty to change the version number to 9.1 assuming that is what you meant. PostgreSQL 8.1.9 has no window function capabilities to begin with. Please change back if I am wrong. – Erwin Brandstetter Oct 27 '11 at 11:51
Rather than assign an alias to the result of the window function, what I'm attempting to do is to calculate a rank() over an alias. I missed out the rank in the original query I posted, I have corrected it now! – KLibby Oct 27 '11 at 12:41
feedback

2 Answers

up vote 1 down vote accepted

Place the alias behind the OVER clause.

SELECT salary
      ,sum(salary) OVER (ORDER BY salary) AS my_alias
FROM   empsalary;

Edit after question update

You cannot reference a column alias at the same level of a SELECT. You need a Sub-SELECT or a CTE for something like that. Like:

SELECT rank() OVER (ORDER BY result) AS rnk
      ,result
FROM  (
    SELECT <compley expression> AS result
    FROM   tbl
    WHERE  <some condition>
    GROUP  BY id
    ) x;

Try this for your query:

SELECT rank() OVER (ORDER BY deltahdlcOverruns) AS rnk
      ,y1_
      ,deltahdlcOverruns
FROM  (
    SELECT this_.deviceNo as y1_
         ,(SELECT _dev_.hdlcOverruns FROM abstractperformancestatistic _abs_ 
           JOIN   enddevicestatistic _dev_ USING (id)
           JOIN   linkstatistic _link_ ON _link_.id = _dev_.linkStatistic_id 
           JOIN   iptstatistic _ipt_ ON _ipt_.id = _link_.iptStat_id 
           WHERE  this_.deviceNo=_dev_.deviceNo AND _abs_.dateTime <= '3910-06-07 00:00:00.0'
           ORDER  BY _abs_.dateTime DESC LIMIT 1
           ) 
           - 
           (SELECT _dev_.hdlcOverruns FROM abstractperformancestatistic _abs_ 
            JOIN   enddevicestatistic _dev_ USING (id)
            JOIN   linkstatistic _link_ ON _link_.id = _dev_.linkStatistic_id 
            JOIN   iptstatistic _ipt_ ON _ipt_.id = _link_.iptStat_id 
            WHERE  this_.deviceNo=_dev_.deviceNo AND _abs_.dateTime >= '3870-06-01 00:00:00.0' 
            ORDER  BY _abs_.dateTime LIMIT 1
           ) AS deltahdlcOverruns
    FROM   EndDeviceStatistic this_ 
    JOIN   AbstractPerformanceStatistic this_1_ USING (id)
    JOIN   AbstractEntity this_2_ USING (id)
    LEFT   JOIN RawEndDeviceStatistic this_3_ USING (id)
    LEFT   JOIN LinkStatistic l2_ ON this_.linkStatistic_id = l2_.id 
    LEFT   JOIN AbstractPerformanceStatistic l2_1_ ON l2_.id=l2_1_.id 
    LEFT   JOIN AbstractEntity l2_2_ ON l2_.id=l2_2_.id 
    LEFT   JOIN RawLinkStatistic l2_3_ ON l2_.id=l2_3_.id 
    LEFT   JOIN IPTStatistic i1_ ON l2_.iptStat_id=i1_.id 
    LEFT   JOIN AbstractPerformanceStatistic i1_1_ ON i1_.id=i1_1_.id 
    LEFT   JOIN AbstractEntity i1_2_ ON i1_.id=i1_2_.id 
    LEFT   JOIN RawIPTStatistic i1_3_ ON i1_.id=i1_3_.id 
    WHERE  this_1_.dateTime between ? and ? 
    GROUP  BY this_.deviceNo
    LIMIT  ?
) x

I made a few additional syntax simplifications.

On a side note:
Don't used reserved words like rank for column names. While rank in particular is allowed in PostgreSQL, it is reserved in SQL:2003 and SQL:2008 standards.

share|improve this answer
OK, not sure what the problem is then. You can always assign an alias to an expression like you do. Maybe you can post your original query? You must have abstracted away the source of the problem, or there is some misunderstanding. – Erwin Brandstetter Oct 27 '11 at 12:43
Thanks for your help, I have posted the full SQL query from the log, hopefully that should show up the problem. – KLibby Oct 27 '11 at 12:57
Thanks, that is what I will need to do, unfortunately it is tricky doing subselects in Hibernate, but that is my next problem to solve! – KLibby Oct 27 '11 at 15:10
feedback
up vote 2 down vote

wrap the windowing in a surrounding query:

SELECT salary, result OVER (ORDER BY result)
FROM (SELECT salary, (...expression...) AS result
      FROM empsalary
     ) x
share|improve this answer
Yes, the subselect is what I need to do. Erwin came up with a similar solution in his answer - if I could accept both answers I would! +1 – KLibby Oct 27 '11 at 15:06
feedback

Your Answer

 
or
required, but never shown
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.