Jody Jody - 3 months ago 18
SQL Question

cross apply sub queries

Hi just learning sql server

this gives me an error :

Incorrect syntax near ')'.'


Query:

select p1.categoryid, p1.productname, p1.unitprice
from production.products as p1 cross apply
(select min(p2.unitprice)
from production.products as p2
where p1.categoryid = p2.categoryid
)

Answer

You still need a table alias and to define the columns:

select p1.categoryid, p1.productname, p1.unitprice
from production.products p1 cross apply
     (select min(p2.unitprice) as minunitprice
      from production.products p2
      where p1.categoryid = p2.categoryid
     ) p2;

You are not selecting anything from the subquery. I assume you intend:

select p1.categoryid, p1.productname, p1.unitprice, p2.minunitprice
from production.products p1 cross apply
     (select min(p2.unitprice) as minunitprice
      from production.products p2
      where p1.categoryid = p2.categoryid
     ) p2;

If so, you can write this as:

select p.*, min(p.unitprice) over (partition by p.categoryid)
from production.products p;
Comments