mysql counting

[monkeytooth]

I have a table that's a few hundred rows. I'm trying to pull out the 3 highest counts. I can't put my finger on how to structure the query though. Ive tried searching for answer's. Ive found the max() option with mysql specifically however that doesn't seem to meet what I am trying to do, so I'm thinking chances are I need to create some kind of loop, but how?

 

So far I can call out all the unique rows, and get a count for each, but I can't figure out how to have it decide which one's were  the highest 3 counts, actually I can't even figure out how I can even get the highest one.

 

I'm shot for ideas, any help would be great.

[fortnox007]

Without calculating

 

SELECT * FROM table ORDER BY `score` DESC LIMIT 3;--  could be something

 

So to make it more clear

SELECT (pretty obvious)

* (all)

FROM table (from your table)

ORDER BY (pretty obvious too)

`score` (the field to order)

DESC (start with highest)

LIMIT 3 (limit results to 3)

 

I had some wine but i am pretty sure this works

 

Btw: this reference gives a nice step by step order of the stuff to put in a query: http://dev.mysql.com/doc/refman/5.0/en/select.html

[gizmola]

You'll have to provide more specifics about the table and what you are after.  Count is a summary operator, so when you say: "3 highest counts" that is confusing.  3 Highest counts of what?  The only way you will get more than one count() is if you are grouping the rows in the table on some set of columns using GROUP BY. 

 

 

[monkeytooth]

Alright, I have 3 rows where I am initially basing this off of.

 

gameID, gamerID, gameReferal

 

gameID is the primary column in the counts as its specific to the game. gamerID is unique to the gamer, and gameReferal is where the owner of gamerID gives out a referal, the referal is then stored next to the new gamerID when the referal signs up.

 

I am trying to count the referals against the gamerID's within any specific gameID. From those counts I am trying to put something together where the top 3 referring gamerID's get tagged as the highest referrers of the time i decide to run the counts. Cause those top 3 get unique items for being the winners so to speak.

 

My problem is I can't wrap my head around a solid idea to generate a count like that. 

[fortnox007]

something like this?

 

SELECT gamerID, COUNT(gameReferal) FROM test_table GROUP BY gamerID DESC LIMIT 3;

 

to make it game specific

 

SELECT gamerID, COUNT(gameReferal) FROM test_table WHERE gameID = 'whatever' GROUP BY gamerID DESC LIMIT 3;

[gizmola]

To do this for a specific game... for this example, I'm going to use gameID = 5.  You would need to provide the right GameID for the game in question.

 

SELECT gamerID, count(*) as countof FROM yourtable WHERE gameID = 5 GROUP BY gamerID  ORDER BY countof DESC LIMIT 3

 

 

[monkeytooth]

Unfortunately thats still not generating what I need/want. I tried playing with it for awhile to do what I want, but it doesn't seem to be taking a count of the gamerID's gameReferral's for any given gameID

 

I'm getting 1-3 results, but none with the actual counts I know exisit, example.. I currently know by doing a bulk count one by one and listing the results that way I have one guy with 11 referrals one with 7 and another with 6.. neither of these three where pulled up in the results with that query unfortunately, I'm still a bit stuck trying to sort it. I know I can generate counts on gamerID with gameReferral against a gameID, and list each one out individually in a bulk list, that I have no problem with. I just wish I could figure out a way to take from that list (its generated with a for loop as far as output goes) and from that list take the top 3 referee's and list them seperately. I just cant fathom how to get it done correctly. I know I am going to need a seperate query of some sort some where to do this, but I'm still lost how to build it.

 

I will say the queries mentions we're helpful though, as I was completely forgetting to limit my results, and then order them by, which helped me try to build something off those queries but I am still stuck

[ninedoors]

Can you post your table structure?

[monkeytooth]

Well, this is slightly different rendition now. But along the same lines as the original post

 

"ID, cID, ticketNum, fromState, toState, fromPhone, SmsSid, SmsMessageSid, when"

 

cID is the gameID

ticketNum is the referral tracking number

ID is the unique ID per person, which is also there referral number in this case.

 

So if person with ID 8 for example sends out 300 referrals, and of those 300 referrals 50 come back and register the registered people that came from the referal with have 8 as there ticketNum. Where as original sign up's without referrals will just get the word "enter" as there ticketNum. Then the thing they will all have in common is the cID, as the cID is in relation to the game thats being run for every one. There are multiple games and people are allowed to sign up more than once so long as the cID represents a different game, and as such the same 50 referals can again sign up under that original ID referral again so long as the cID is different from one they already registered for. That all said.

 

I am trying to take a count on the ID's where the ticketNum matches the ID its counting for, that has the same cID thats being used to generate the count within any given game campaign. This count is for a master list on the admin side for the client I am working for. He doesn't want to have to manually go through what will eventually be thousands of entries doing manual counts or even listing them out in bulk like I already have with a count next to each one listed.

 

He would like to have a list of 3 to 5 people that are the top referree's of any given game so he can delve out the prizes to the top dogs so to speak, and there lies my issue, how do I take a master count like that, of who has the highest referral count. Kinda wish this was a project being built from scratch but its building on something that already exists, which is where my issue is. cause I can't necessarily go in and change to much of whats already working for the purpose of this count.

 

So as such, is something like this even remotely possible is there a way to get a master count then pick from said master count so to speak of which ones had the highest, i mean I know its not as simple as just a little one line query (i don't think at least), but where to begin to generate the information like that in the first place is beyond me at the moment. Either I am thinking way to far out of the box and the answer is that simple, or im not thinking far enough out of the box and its a little more complex of a task then I want to give it credit for.

[gizmola]

I don't know how to say this any clearer than I already did.  What is preventing you from implementing the query that was already provided you, using the table structure you have?