CODEWITHSUNDEEP
sqlsql-server
BenNowak
BenNowak

Reputation: 13

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. - error

I got problem with second question, becasue i receive error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.". Can you help me?

Select P.Name, P.ProductCategoryID, PC.Name, Max(P.ListPrice) as MaxPrice
from SalesLT.Product as P
join SalesLT.ProductCategory as PC
on PC.ProductCategoryID=P.ProductCategoryID
where P.ListPrice=
    (select Max(ListPrice)
    from SalesLT.Product as P2
    where P2.ProductCategoryID=P.ProductCategoryID)
group by P.Name, P.ProductCategoryID, PC.Name
order by MaxPrice desc;

go

with sale_cte as 
    (Select PC.Name, P.ProductCategoryID, P.Listprice,
    ROW_NUMBER() over(partition by PC.Name order by P.Listprice) as RN
    from SalesLT.Product as P
    join SalesLT.ProductCategory as PC
    on PC.ProductCategoryID=P.ProductCategoryID)
Select PC.Name,
    (select *
    from sale_cte)
from SalesLT.ProductCategory as PC
wher RN=1

Upvotes: 0

Views: 153

Answers (2)

Rahul
Rahul

Reputation: 77896

I think the error is in below part

Select PC.Name,
    (select *
    from sale_cte)
from SalesLT.ProductCategory as PC
where RN=1

This can be re-written as

select * from sale_cte
wher RN=1

Upvotes: 3

ramana_k
ramana_k

Reputation: 1933

You don't need to list PC.Name separately, as it is part of the whole list of columns (*)

Upvotes: 1

Related Questions