Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 341
  • Last Modified:

use inner query result in main query order by

Hello experts,

i have inner queries results in main query and i want to sort that inner query result using order by tag in main query.

say for example:

Tablename:  Product

Tablename: Product_review

Relation: both tables connected using Property table id and Product_review table productid

so it will come like

Select p.*,(select Count(*) from Product_review pr where pr.productid=p.id) as pro_review from Product p

Now what i want is something like :

Select p.*,(select Count(*) from Product_review pr where pr.productid=p.id) as pro_review from Product p order by pro_review desc

but its not working when i run its asking me pro_review value as its not part of main tables but its a part of query result.

How do we achieve it using single sql query ?

Thanks
Kinjal
0
dotsandcoms
Asked:
dotsandcoms
  • 4
1 Solution
 
derekkrommCommented:
select * from (Select p.*,(select Count(*) from Product_review pr where pr.productid=p.id) as pro_review from Product p) a order by pro_review desc
0
 
YZlatCommented:
try something like this

SELECT p.*, COUNT(pr.productid) As pro_review
FROM Product as p
INNER JOIN Product_review pr ON p.id=pr.productid
ORDER BY COUNT(pr.productid) DESC
0
 
YZlatCommented:
SELECT p.*, COUNT(pr.productid) As pro_review
FROM Product as p
INNER JOIN Product_review as pr ON p.id=pr.productid
ORDER BY COUNT(pr.productid) DESC
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
YZlatCommented:
actually you cna't do p.*, you will have to list all the columns and then add a GROUP BY clause
0
 
YZlatCommented:

SELECT p.id, p.ProductTitle, p.AnotherField, COUNT(pr.productid) As pro_review
FROM Product as p
INNER JOIN Product_review as pr ON p.id=pr.productid
GROUP BY p.id, p.ProductTitle, p.AnotherField
ORDER BY COUNT(pr.productid) DESC
0
 
dotsandcomsAuthor Commented:
simply best
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now