CODEWITHSUNDEEP
sqlamazon-redshift
Kevin Nash
Kevin Nash

Reputation: 1561

SQL / Redshift Error - Filtering only first occurence of a value

I am trying to return the first occurence of a value using the below SQL script:

SELECT a.store_id, b.store_name, c.prod_id FROM
  (
select a.store_id as sh, b.store_name, c.prod_id
count(1) ct,row_number() over  (partition by store_id order by store_id 
desc) row_num
from stores a, store_details b , product c
where a.id=b.store_id  and a.prod_id = c.id and  b.id = 101
group by a.store_id as sh, b.store_name, c.prod_id,
)t
WHERE row_num = 1;

I get an error

Invalid operation : relation "a" does not exist.

I am using a Redshift DB. Could anyone assist on this. Thanks..

Upvotes: 0

Views: 83

Answers (1)

Markov
Markov

Reputation: 150

You are selecting from a subquery, and the alias for it is "T", you cannot reference subquery alias from outside the subquery.

SELECT t.store_id, T.store_name, T.prod_id 
FROM
  (
   select a.store_id as sh, b.store_name, c.prod_id
   count(1) ct,row_number() over  (partition by store_id order by store_id 
   desc) row_num
   from stores a, store_details b , product c
   where a.id=b.store_id  and a.prod_id = c.id and  b.id = 101
   group by a.store_id as sh, b.store_name, c.prod_id,
)T
WHERE t.row_num = 1;

Upvotes: 2

Related Questions