How to use new created column in where column in sql?

Question

Hi I have a query which looks like the following :

                SELECT device_id, tag_id, at, _deleted, data, 
                row_number() OVER (PARTITION BY device_id ORDER BY at DESC) AS row_num
                FROM mdb_history.devices_tags_mapping_history
                WHERE at <= '2019-04-01' 
                AND _deleted = False
                AND (tag_id = '275674' or tag_id = '275673')
                AND row_num = 1

However when I run the following query, I get the following error :

ERROR:  column "row_num" does not exist

Is there any way to go about this. One way I tried was to use it in the following way:

SELECT * from (SELECT device_id, tag_id, at, _deleted, data, 
                        row_number() OVER (PARTITION BY device_id ORDER BY at DESC) AS row_num
                        FROM mdb_history.devices_tags_mapping_history  
                        WHERE at <= '2019-04-01' 
                        AND _deleted = False
                        AND (tag_id = '275674' or tag_id = '275673')) tag_deleted 
                WHERE tag_deleted.row_num = 1

But this becomes way too complicated as I do it with other queries as I have number of join and I have to select the column as stated from so it causes alot of select statement. Any smart way of doing that in a more simpler way. Thanks

No, there is no other way. You can't access a column alias right in the WHERE clause. You will have to use a derived table. But I don't understand what that has to do with JOINS and why it would cause "a lot of select statements". The derived table does not any performance overhead if that is what you are concerned about — , Mar 17 '20 at 16:59

score 1 · Accepted Answer · answered Mar 17 '20 at 17:01

You can't refer to the row_num alias which you defined in the same level of the select in your query. So, your main option here would be to subquery, where row_num would be available. But, Postgres actually has an option to get what you want in another way. You could use DISTINCT ON here:

SELECT DISTINCT ON (device_id), device_id, tag_id, at, _deleted, data
FROM mdb_history.devices_tags_mapping_history
WHERE
    at <= '2019-04-01' AND
    _deleted = false AND
    tag_id IN ('275674', '275673')
ORDER BY
    device_id,
    at DESC;

This is simpler and I was looking for the similar thing. thanks — Mustufain, Mar 17 '20 at 17:47

score 1 · Answer 2 · answered Mar 17 '20 at 20:23

Too long/ formatted for a comment. There is a reason behind @TimBiegeleisen statement "alias which you defined in the same level of the select". That reason is that all SQL statement follow the same sequence for evaluation. Unfortunately that sequence does NOT follow the sequence of clauses within the statement presentation. that sequence is in order:

from
where
group by
having
select
limits

You will notice that what actually gets selected fall well after evaluation of the where clause. Since your alias is defined within the select phase it does not exist during the where phase.

How to use new created column in where column in sql?

2 Answers2

Linked

Related