Re: Counting events and getting top 5 matches

The only code you show is SQL, so I'm giving you an SQL answer:

select code, username, userid, count(*) as code_count
group by code, username, userid
order by code, code_count
[download]

To get the "top five" out of that select statement, you can use the RANK() function:

select 
    code, 
    username, 
    userid, 
    count(*) as code_count,
    rank() over (ORDER BY code_count DESC) as code_rank
where code_rank <= 5
group by code, username, userid
[download]

The rank() function doesn't seem to be supported on Sybase ASE. Maybe you can create the select as a view and then use some rowid trickery to get at the top five users per code group.

Comment on Re: Counting events and getting top 5 matches Select or Download Code

Replies are listed 'Best First'.
Re^2: Counting events and getting top 5 matches by psini (Deacon) on Sep 17, 2008 at 09:11 UTC
++ BTW, I'm a strong supporter of the view that when you can have the DBMS do the work, you must. You never know when your DB will be on a different machine and having tons of data flowing around the net only to arrive to a program that count them is... illogical. Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."	[reply]
Re: Counting events and getting top 5 matches by jonadab (Parson) on Sep 17, 2008 at 11:37 UTC
I'm a strong supporter of the view that when you can have the DBMS do the work, you must. I'm not so sure that's always the case. There can be a real tradeoff between performance and maintainability there. I've seen some of the hairy twenty-page stored procedures people write when they're trying to do something in SQL that is fundamentally better suited to a general-purpose high-level language and would be less than a screenful of clean, easy-to-read Perl. Certainly, there are times when it's better to have the DBMS do the work... -- We're working on a six-year set of freely redistributable Vacation Bible School materials.	[reply]
Re^2: Counting events and getting top 5 matches by BrowserUk (Patriarch) on Sep 17, 2008 at 15:14 UTC
Get the best of both worlds. Use PL/Perl to write your SP in a GP that you're familiar with. Then run it where the data is and only tranfer the results. Rather than transferring all the data to where perl, is only to throw some large percentage of it away. Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error. "Science is about questioning the status quo. Questioning authority". In the absence of evidence, opinion is indistinguishable from prejudice. "Too many [] have been sedated by an oppressive environment of political correctness and risk aversion."	[reply]
Re^2: Counting events and getting top 5 matches by psini (Deacon) on Sep 17, 2008 at 12:48 UTC
Agree. My point is that it is useless (== bad) doing a "generic" SELECT and then filtering or aggregating the data client side. Transferring business logic into stored procedures is another matter: I tend to use SP only to guarantee data integrity, where referential integrity rules are not enough. Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."	[reply]
Re^2: Counting events and getting top 5 matches by moritz (Cardinal) on Sep 17, 2008 at 09:11 UTC
without `rank()` you can simply add a `limit 5`. Or am I missing some obvious glitch here?	[reply] [d/l] [select]
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` [download] 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).	[reply] [d/l] [select]
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."	[reply]
Re^2: Counting events and getting top 5 matches by iphony (Acolyte) on Sep 17, 2008 at 08:51 UTC
Thanks for the help. Actually I wanted to do the operation in Perl, as I already got the sql result into an array. I will try and see how it goes by doing it in SQL.	[reply]