SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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...
    Code MySQL:
     
    pk venuename    userID
    1  londontower  user1
    2  londontower  user2
    3  londontower  user1
    4  londontower  user1
    5  londoneye     user1
    6  londoneye     user2

    kind regards,

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    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).

    Code SQL:
    SELECT TOP 2 userID
    , venuename
    , COUNT(*) AS venuecount
    FROM venues v
    GROUP BY userid, venuename
    ORDER BY venuecount DESC
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey Dave,

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

    That is some funny looking code you have there....

  4. #4
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhh I can see your code now, must have been a glitch.

    What would be the equivalent code for mySQL please?

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Based on a quick google search, I would venture to guess this might be on the right track for mySQL

    Code SQL:
     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
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Based on a quick google search, I would venture to guess this might be on the right track for mySQL

    Code SQL:
     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

    Thankyou for taking the time to help me out!

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,290
    Mentioned
    122 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by johnuk View Post
    That worked perfect, you are clearly an SQL genius - I also learnt something too

    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!
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    38911 Basic Bytes Free johnuk's Avatar
    Join Date
    Jul 2008
    Location
    Somerset, England
    Posts
    459
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    many thanks again


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •