Using an alias in a window function in a query in PostgreSQL

Question

I am using PostgreSQL version 9.1 and watching Postgres docs , I know that the following can be done:

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

and this works for my queries.

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

I tried this:

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

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

and I get the error message column "result" does not exist.

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

EDIT:

I am using Hibernate, with some native SQL for the window function. The full SQL that is generated and run is shown 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 ? 

Answer 1

Put an alias behind the OVER clause.

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

Edit after updating question

You cannot reference a column alias at the same SELECT level. For something like this, you need a Sub-SELECT or CTE . How:

 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 request:

 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:
Do not use reserved words , such as rank for column names. Although rank , in particular, is permitted in PostgreSQL, it is reserved in the SQL: 2003 and SQL: 2008 standards.

Answer 2

wrap the window in the surrounding query:

 SELECT salary, result OVER (ORDER BY result) FROM (SELECT salary, (...expression...) AS result FROM empsalary ) x