Link to home
Start Free TrialLog in
Avatar of SheppardDigital
SheppardDigital

asked on

Count rows in a MySQL statement, but don't return the value in the results

I have a query where I'm trying to return recipes in a database that have been liked the most, the problem is I don't want the result of COUNT to be included in the results. I only want to use the value of 'cnt' in the query itself, and not return it.

Here's my query;
SELECT recipe.*, count(*) AS cnt FROM recipe_likes INNER JOIN recipe ON recipe.id = recipe_likes.recipe_id GROUP BY recipe_id ORDER BY cnt DESC

Open in new window


The reason I don't want it returning is that I'm using Java, I'm mapping the results to a recipe Object which doesn't include a property for 'cnt', so the results can't be mapped.

Is there a way to use 'cnt' in the query but not return it?
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi,
Pls try

SELECT k.*
FROM 
(
	SELECT recipe.*, count(*) AS cnt FROM recipe_likes INNER JOIN recipe ON recipe.id = recipe_likes.recipe_id 
	GROUP BY recipe_id
) k
ORDER BY cnt

Open in new window


Hope it helps !
Avatar of SheppardDigital
SheppardDigital

ASKER

Thanks, but that still returns 'cnt' in the results.
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Geert G
the nesting is not necessary

SELECT recipe.Id, recipe.Name 
FROM recipe_likes 
  INNER JOIN recipe ON recipe.id = recipe_likes.recipe_id 
GROUP BY recipe_id
order by count(*) desc

Open in new window