#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    145
    Rep Power
    15
    Got a good one (or maybe I have just been working to hard)

    I need to dive into a database that tracks hits on another database to see what is the most poular record (or recoreds).

    I am using the following SQL code

    _______ code _______
    SELECT listingid,COUNT(*) from listingstats GROUP by listingid
    _______ code end ________

    This is getting me a count of all the items, and there respective hits, but it is not in any order of importance - if I order it in mysql, I can not order by the count.

    Is there anyway I can use a SELECT statement like that to get a row selection from mySQL to where the item with the most rows (hits) would be the top and others following it in there hit aspects would be lower in a decreasing manner?

    Any help would be appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    63
    Rep Power
    15
    You could try this

    SELECT listingid,COUNT(*) as numlistings from listingstats GROUP BY listingid ORDER BY numlistings DESC;
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Posts
    145
    Rep Power
    15
    Thanks man - worked like a charm... :-)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Posts
    63
    Rep Power
    15
    No problem.

Similar Threads

  1. Replies: 14
    Last Post: February 8th, 2004, 02:28 PM
  2. Sorting MySql results by nearest to furthest away
    By highlander5430 in forum MySQL Help
    Replies: 7
    Last Post: January 25th, 2004, 02:40 PM
  3. normalised design causing problems displaying results
    By emilyb in forum MS SQL Development
    Replies: 14
    Last Post: January 21st, 2004, 07:02 PM
  4. Problem with Count () on MySQL 4.0.16
    By Chevalric in forum MySQL Help
    Replies: 4
    Last Post: January 16th, 2004, 08:51 PM
  5. Overwriting mysql query with the query results
    By onerandomday in forum PHP Development
    Replies: 1
    Last Post: January 14th, 2004, 05:45 AM

IMN logo majestic logo threadwatch logo seochat tools logo