CODEWITHSUNDEEP
javahibernatecriteria
skamlet
skamlet

Reputation: 691

Select query with order by in criteria

I'd like to create a criteria query from this SQL query :

SELECT * FROM `Product` p
ORDER BY (SELECT COUNT(*) FROM `Sale` WHERE `product_id` = p.id) DESC

I cannot find a way to do it with the sub query...

Upvotes: 0

Views: 199

Answers (2)

Amar
Amar

Reputation: 12010

Order By is only applicable for a column which is present in the select part.

So basically something like this will work:

SELECT Group, COUNT(*) as total
FROM table
GROUP BY Group
ORDER BY total DESC

So for your case, you can do something like following:

SELECT *, (SELECT COUNT(*) FROM `Sale` WHERE `product_id` = p.id) as total FROM `Product` p
ORDER BY total DESC

Note: This is completely untested.

Upvotes: 1

Brad
Brad

Reputation: 1367

Try an implicit join:

select *
from (
    select 
         p.*, 
         (select count(*) from sale s where s.product_id = p.id) as cnt 
    from product p
)a 
order by cnt desc

Upvotes: 1

Related Questions