Using an alias in a window function in a query in PostgreSQL
wrap the windowing in a surrounding query:
SELECT salary, result OVER (ORDER BY result)
FROM (SELECT salary, (...expression...) AS result
FROM empsalary
) x
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 (an "output column") at the same level of a SELECT
. You need a subselect or a CTE for this. Like:
SELECT id, result, rank() OVER (ORDER BY result) AS rnk
FROM (
SELECT id, <complex expression> AS result
FROM tbl
WHERE <some condition>
GROUP BY id
) sub;
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.