The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Databases
> MySQL Help
|
Grab results with only the same top count number
Discuss Grab results with only the same top count number in the MySQL Help forum on Dev Shed.
Grab results with only the same top count number MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
December 31st, 2012, 03:03 PM
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 15
Time spent in forums: 6 h 59 m 40 sec
Reputation Power: 0
|
|
|
Grab results with only the same top count number
What I'm trying to do is count the number of times a a column record is present and then display the top result. With this normally I would do "Limit 1,0" however in this instance I don't want to limit it to a single result as there might be times where there could be several records sharing the same top count number. For example:
Code:
----------------
| Name | Count |
----------------
| Usr1 | 4 |
| Usr2 | 2 |
| Usr3 | 4 |
| Usr4 | 3 |
----------------
As you can see if I were to order the results by count and then by name I would get Usr1, Usr3, Usr4, Usr2 in that order. If I added a limit to 1,0 then I would get just Usr1. I could put a limit of 2 but how would I know there would be 2 users?
If I had 100 different users and there were 10 users that shared the same top count how would I got about grabbing only those 10 users that share the same top count number? Also is it possible to grab the lowest count number?
|
December 31st, 2012, 05:25 PM
|
|
|
Assign them a rank then add a clause 'allowing' only those of a given rank, or less.
__________________
The moon on the one hand, the dawn on the other:
The moon is my sister, the dawn is my brother.
The moon on my left and the dawn on my right.
My brother, good morning: my sister, good night.
-- Hilaire Belloc
|
December 31st, 2012, 07:16 PM
|
 |
Contributing User
|
|
Join Date: Dec 2012
Location: Chicago
Posts: 51
Time spent in forums: 17 h 43 m 32 sec
Reputation Power: 1
|
|
|
You could order the results by count and then by name and set limit to some arbitrarily large number. Then ignore all remaining returned results when the count changes.
|
December 31st, 2012, 07:36 PM
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 15
Time spent in forums: 6 h 59 m 40 sec
Reputation Power: 0
|
|
That's what I've been trying to do but couldn't work it out, as soon as the count changes I don't know how to suddenly cut out the rest. My current SQL is like this:
PHP Code:
SELECT
site_games_list.box_art AS image,
site_games_list.name AS game,
COUNT(site_gift_transactions.game) AS top
FROM site_gift_transactions
INNER
JOIN site_games_list
ON site_games_list.id = site_gift_transactions.game
WHERE moderated = 1
GROUP BY game
ORDER BY top DESC, game
LIMIT 1,0
The tables are as follows (in regards to the relevant columns used):
Code:
--------------------------
| site_gift_transactions |
--------------------------
| id | int(10) |
| moderated | int(1) |
| game | int(6) |
--------------------------
--------------------------
| site_games_list |
--------------------------
| id | int(6) |
| name |varchar(100)|
| box_art |varchar(100)|
--------------------------
|
January 1st, 2013, 06:21 AM
|
 |
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
Quote: | Originally Posted by Varsh ...I don't know how to suddenly cut out the rest. | if you're going to use richpri's method, you have to use a number higher than 1 in your LIMIT clause, try something like 15, and then check for the change in count using your application language (php or whatever)
better would be to use rank as simon suggested
|
January 1st, 2013, 08:08 AM
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 15
Time spent in forums: 6 h 59 m 40 sec
Reputation Power: 0
|
|
Yeah I've been trying the rank method without too much luck, like you said I will need to use the rank method. I've got a few problems though.
Problem 1:
I've tried integrating the RANK with my current SQL and while it seems like it might work the RANK just keeps set to zero. I put "etc" for the image deliberately.
PHP Code:
SET @rownum = 0, @rank = 0, @prev_val = NULL;
SELECT
site_games_list.box_art AS image,
site_games_list.name AS game,
COUNT(site_gift_transactions.game) AS top,
@rownum := @rownum + 1 AS row,
@prev_val := COUNT(site_gift_transactions.game) AS prev,
@rank := IF (
@prev_val != COUNT(site_gift_transactions.game),
@rownum,
@rank
) AS rank
FROM site_gift_transactions
INNER
JOIN site_games_list
ON site_games_list.id = site_gift_transactions.game
WHERE moderated = 1
GROUP BY game
ORDER BY top DESC, game
Code:
---------------------------------------------------
| image | game | top | row | prev | rank |
---------------------------------------------------
| etc | Alan Wake | 2 | 3 | 2 | 0 |
| etc | Anno 2070 | 2 | 5 | 2 | 0 |
| etc | AaAaAA!!! | 1 | 4 | 1 | 0 |
| etc | Dungeon Siege | 1 | 1 | 1 | 0 |
| etc | The Sims 3 | 1 | 2 | 1 | 0 |
---------------------------------------------------
Problem 2:
This is trying another method from scratch with just the "game" column. It seems like everything is working but the rankings are all messed up plus the first rank is set to zero, I assume this is because I'm taking the rankings from the "game" rather than the COUNT, but if I took it from the COUNT it won't work and just give a ranking of zeros. Here's what I end up with (right is with "game", left is with COUNT):
PHP Code:
SET @rownum = 0, @rank = 0, @prev_val = NULL;
SELECT
@rownum := @rownum + 1 AS row,
@rank := IF (
@prev_val!=game,
@rownum,
@rank
) AS rank,
@prev_val := game AS game,
count(game) as total
FROM (
SELECT game
FROM site_gift_transactions
WHERE moderated = 1
ORDER BY game
) site_gift_transactions
group by game
ORDER BY count(game) desc, game
====================================
SET @rownum = 0, @rank = 0, @prev_val = NULL;
SELECT
@rownum := @rownum + 1 AS row,
@prev_val := count(game) AS prev,
@rank := IF (
@prev_val!=count(game),
@rownum,
@rank
) AS rank,
game,
count(game) as total
FROM (
SELECT game
FROM site_gift_transactions
WHERE moderated = 1
ORDER BY game
) site_gift_transactions
group by game
ORDER BY count(game) desc, game
Code:
------------------------------------- ------------------------------
| row | prev | rank | game | total | | row | rank | game | total |
------------------------------------- ------------------------------
| 3 | 2 | 0 | 324 | 2 | | 3 | 3 | 324 | 2 |
| 4 | 2 | 0 | 335 | 2 | | 4 | 4 | 335 | 2 |
| 1 | 1 | 0 | 213 | 1 | | 1 | 0 | 213 | 1 |
| 2 | 1 | 0 | 226 | 1 | | 2 | 2 | 226 | 1 |
| 5 | 1 | 0 | 528 | 1 | | 5 | 5 | 528 | 1 |
------------------------------------- ------------------------------
|
January 1st, 2013, 09:57 AM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
dude...
run this --
Code:
SELECT t.image
, t.game
, t.top
FROM ( SELECT site_games_list.id
, site_games_list.box_art AS image
, site_games_list.name AS game
, COUNT(site_gift_transactions.game) AS top
FROM site_games_list
INNER
JOIN site_gift_transactions
ON site_gift_transactions.game = site_games_list.id
AND site_gift_transactions.moderated = 1
GROUP
BY site_games_list.id ) AS t
WHERE ( SELECT COUNT(*)
FROM ( SELECT site_games_list.id
, COUNT(*) AS top
FROM site_games_list
INNER
JOIN site_gift_transactions
ON site_gift_transactions.game = site_games_list.id
AND site_gift_transactions.moderated = 1
GROUP
BY site_games_list.id ) AS x
WHERE x.top > t.top ) = 0
|
January 1st, 2013, 12:28 PM
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 15
Time spent in forums: 6 h 59 m 40 sec
Reputation Power: 0
|
|
Oh wow that worked perfectly.
Sorry if I've been coming across a bit dim witted as I've never come across the RANK function before let alone do any nested queries. I have been trying all day and a fair bit last night so I definitely gave it a try.
Looks like I need to get a dedicated MySQL book. 
|
January 1st, 2013, 04:18 PM
|
|
SQL Consultant
|
|
Join Date: Feb 2003
Location: Toronto Canada
|
|
thanks for the kind words
this type of problem, in my opinion, is advanced sql
so don't kill yourself if you can't do it right away
it will come with practice
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
