• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

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

0
rgb192
Asked:
rgb192
  • 3
  • 2
  • 2
  • +1
1 Solution
 
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
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
SharathData 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now