Results 1 to 2 of 2

Thread: SQL query help

  1. #1
    Join Date
    Apr 2011

    Question Unanswered: SQL query help


    I have 2 Tables (keywords & archive). The archive table is my main table and stores data for submitted pictures. The keywords table stores all keywords used in my site with 3 columns (id, keyword & numclicks). Currently I have a section on my site (OutOfRegs - Your source for military humor!) that lists the top 20 keywords and orders them by the 'numclicks' column. There is a column in the archives table titled 'keywords' and contains a string of keywords separated by a comma (ex: military, army, fail). I want to perform a query that groups keywords by the number of times they show up in 'archive.keywords', and then order the results by 'keywords.numclicks'. This query would be better than the previous because it would order keywords by their frequency on the site aas well as their number of clicks. Any ideas?

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    comma-delimited values inside a single column violates first normal form, and the penalty you pay is complex queries and really terrible performance
    SELECT k.keyword
         , COUNT(a.keyword) AS times_in_archive
      FROM keywords AS k
      JOIN archive AS a
        ON FIND_IN_SET(k.keyword,a.keyword) > 0
        BY k.keyword | @rudydotca
    Buy my SitePoint book: Simply SQL

Tags for this Thread

Posting Permissions

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