rgb192
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
I want to order by count of productid
select * from orderitems i
left join products p on p.productid=i.productid
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you look at my solution #34900714
ASKER
>>Did you look at my solution #34900714
returned only null and 1 for productcount
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
,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
ASKER
i have a similar question
https://www.experts-exchange.com/questions/26823686/adding-latest-datetime-to-existing-query.html
https://www.experts-exchange.com/questions/26823686/adding-latest-datetime-to-existing-query.html
select i.productid,COUNT(*) cnt
from orderitems i
left join products p on p.productid=i.productid
order by cnt