Link to home
Start Free TrialLog in
Avatar of rgb192
rgb192Flag for United States of America

asked on

order by count

productid is int
I want to order by count of productid


select * from orderitems i
left join products p on p.productid=i.productid

Avatar of Sharath S
Sharath S
Flag of United States of America image

Are you looking for something like this?
select i.productid,COUNT(*) cnt
  from orderitems i
  left join products p on p.productid=i.productid
 order by cnt
select *  
from orderitems i
left join (select productid, COUNT(1) as ProductCount
           from products
           group by productid) p on p.productid = i.productid
order by p.ProductCount
Avatar of rgb192

ASKER

Msg 8120, Level 16, State 1, Line 1
Column 'orderitems.productid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Did you look at my solution #34900714
Avatar of rgb192

ASKER

>>Did you look at my solution #34900714  
returned only null and 1 for productcount
select *
 ,count(i.productid) over (order by productid) as ct
from orderitems i
left join products p on p.productid=i.productid
order by ct desc