dotsandcoms
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
FROM Product as p
INNER JOIN Product_review as pr ON p.id=pr.productid
ORDER BY COUNT(pr.productid) DESC
actually you cna't do p.*, you will have to list all the columns and then add a GROUP BY clause
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
ASKER
simply best
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