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

LVL 1
rgb192Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
I missed group by.

select i.productid,COUNT(*) cnt
  from orderitems i
  left join products p on p.productid=i.productid
 group by i.productid
 order by cnt
0
 
SharathData EngineerCommented:
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
0
 
Ephraim WangoyaCommented:
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
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
rgb192Author Commented:
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.
0
 
Ephraim WangoyaCommented:
Did you look at my solution #34900714
0
 
rgb192Author Commented:
>>Did you look at my solution #34900714  
returned only null and 1 for productcount
0
 
LowfatspreadCommented:
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
0
All Courses

From novice to tech pro — start learning today.