0

I want to use the same subquery in from and where clause. Tried following two approaches, but getting same error in both cases at different places in the query.

QUERY 1:

select * from (subquery1) as t_feature where id = (select MAX(id) from t_feature);

QUERY 2:

select * from t_feature where id = (select MAX(id) from (subquery1) as t_feature);

ERROR:

ERROR: relation "t_feature" does not exist

For temporary soultion, I have created a view for the subquery, and used that in place of the subquery. But I do not want to create views for this case.

S.K
  • 480
  • 1
  • 4
  • 19
  • maybe You should look at rank() function. I posted example here: https://stackoverflow.com/questions/16892902/postgresql-inner-join-two-tables-with-a-limit/60720220#60720220 – transcendx Mar 17 '20 at 10:27

1 Answers1

1

Use a common table expression:

with t_feature as (
   ...
) 
select * 
from t_feature 
where id = (select MAX(id) from t_feature);
  • Tried using common table expression, it worked. But not sure how `distinct` can help here. Distinct eliminated duplicate rows, but I want to pick the row with max value, and there can be multiple rows with the same MAX value. – S.K Mar 17 '20 at 09:59
  • @S.K: then I mis-understood your intentions –  Mar 17 '20 at 10:11