On Thu, 13 Jun 2013 08:13:29 -0400 Richard Hipp <d...@sqlite.org> wrote:
> SELECT > name, > (SELECT COUNT(food_id) FROM foods_episodes WHERE food_id=f.id) count > FROM > foods f > ORDER BY count DESC LIMIT 10; ... > SELECT > name, > (SELECT COUNT(*) FROM foods_episodes WHERE food_id=f.id) count > FROM > foods f > ORDER BY count DESC LIMIT 10; Why not simply SELECT f.name, count(e.food_id) as 'episodes' FROM foods as f OUTER JOIN foods_episodes as e ON f.id = e.food_id GROUP BY f.name ORDER BY episodes DESC LIMIT 10; This form expresses the query more algebraically and IMO is much easier to understand. Last I checked, the use of a subquery in the SELECT clause isn't even stardard SQL. (FWIW stylistically, I avoid "count" as a column name, both because it's a keyword, and because it's vague. A concrete noun in plural form is more expressive.) --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
