Help needed on an SQL query

Hey all,

I have a table which is for recording visits to specific tourist venues.

Everytime a person visits a venue, a record is added to the table to the effect. There is a varchar field which holds the venue name.

What I would like is a query which returns the most popular venue on the table for specific user, is this possible?

e.g…



pk venuename    userID
1  londontower  user1
2  londontower  user2
3  londontower  user1
4  londontower  user1
5  londoneye     user1
6  londoneye     user2


kind regards,

What database are you using? That’ll make a difference on the syntax you need to use.

For sql server, this will work but you would need to know the number of users in the list for the top number (ie if you have 10 users, then instead of top 2 you would have top 10).


SELECT TOP 2 userID
, venuename
, COUNT(*) AS venuecount
FROM venues v
GROUP BY userid, venuename
ORDER BY venuecount DESC

Hey Dave,

thanks for your reply, sorry I did not mention, it is mySQL.

That is some funny looking code you have there… :rofl:

Ahhh I can see your code now, must have been a glitch.

What would be the equivalent code for mySQL please? :slight_smile:

Based on a quick google search, I would venture to guess this might be on the right track for mySQL


 SELECT userID
          , venuename
          , COUNT(*) AS venuecount
   FROM venues v
 GROUP BY userid, venuename
HAVING venuecount = (SELECT COUNT(*) AS vcount2
                                   FROM venues v2
                                 GROUP BY userid, venuename
                                 ORDER BY vcount2 DESC limit 1)
ORDER BY venuecount DESC

That worked perfect, you are clearly an SQL genius - I also learnt something too :wink:

Thankyou for taking the time to help me out!

Nope, just know enough to be dangerous, and can google the rest!

Glad it worked out for you! :slight_smile:

many thanks again :eye: