Question: Would it be possible to write a twisted-powered application that opens an interactive shell, e.g. to tweak the business objects served via the twisted protocol layer? 

My current problem is that the `reactor.run()` blocks the application, and the `IPython.embed()` is only run *after* the reactor has finished.

Answer: 
If `COUNT` is all you want, you can do it with a subquery rather than a join, like this:

```
SELECT u.*
,      (SELECT COUNT(*) FROM notes WHERE notes.user_id=u.id) as amountnotes
FROM users u
ORDER BY amountnotes DESC

```

If you do it the `JOIN` way, your `GROUP BY` *should* list all columns of the `user` (although MySql is one of the RDBMS systems that does not require this, porting such SQL to databases of other vendors becomes problematic).
Your query will return the resultset you specified, assuming that `id` is UNIQUE (or PRIMARY) KEY in `users` table. Your query looks fine for MySQL.

Best practice is to list out the expressions you want returned, rather than using the `*`; but what you have is valid.

Note that this query will work in MySQL, but other databases will throw an error, because there are non-aggregates in the SELECT list that are not included in the GROUP BY.

(Your query isn't the only way to get the specified resultset, there are several alternatives).