in reply to Re: Counting events and getting top 5 matches
in thread Counting events and getting top 5 matches

without rank() you can simply add a limit 5.

Or am I missing some obvious glitch here?

Replies are listed 'Best First'.
Re^3: Counting events and getting top 5 matches
by Corion (Patriarch) on Sep 17, 2008 at 09:18 UTC

    This will give you the overall top 5 users, not the top five users per code group. Given:

    code userid count 1 corion 1000 2 corion 999 3 corion 888 4 corion 777 5 corion 6 1 moritz 555 2 moritz 444 3 moritz 333 4 moritz 222 5 moritz 1

    ordering by count,code would never show moritz, and you'd always miss out on low-counting codes (like code 5). Ordering by code, count would leave out codes with very few users (if you have 1000 users of code 1 and only 3 of code 5).

Re^3: Counting events and getting top 5 matches
by psini (Deacon) on Sep 17, 2008 at 09:22 UTC

    With LIMIT 5 you get the top 5 within the whole table. What the OP wanted is the top 5 for every value of code. It can be obtained without RANK but you need a subquery (or perhaps two) and this can reduce performances.

    Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."