Most Popular In Column

Discussion in 'Databases' started by timallard, Apr 30, 2009.

  1. #1
    Hello,

    Im trying to create a query to get the most popular.. heres an example.

    I have a table called items. I have a column called colors.

    I successfully did a row count echoing out:

    2 - red
    3 - green
    6 - black
    1 - white

    How can i get the largest value?

    I then took it one step further and ORDER BY ASC so essentially this is what i have

    SELECT items, COUNT(items) FROM colors GROUP BY color ORDER BY color ASC"

    so right now im listing out the colors, and its tallying them. How can I get it to give me the largest tally?

    Im kinda new to this.. so.. all help is appreciated. thanks!!
     
    timallard, Apr 30, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    Maybe something like:

    SELECT items, COUNT(items) AS item_count FROM colors GROUP BY color ORDER BY item_count DESC
     
    jestep, Apr 30, 2009 IP
  3. timallard

    timallard Well-Known Member

    Messages:
    1,634
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    158
    #3
    hmm I am having a little trouble with your query string jestep. Im getting confused with the AS item_count part, can you explain that a little bit please?


    Thanks!
     
    timallard, Apr 30, 2009 IP
  4. freelistfool

    freelistfool Peon

    Messages:
    1,801
    Likes Received:
    101
    Best Answers:
    0
    Trophy Points:
    0
    #4
    All "AS item_count" does is give the count a column name that you can reference in the order by clause. He's using it to sort the list from highest to lowest (descending).
     
    freelistfool, Apr 30, 2009 IP
    timallard likes this.
  5. chisara

    chisara Peon

    Messages:
    141
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #5
    What jesteps query does is the following:
    SELECT items, COUNT(items) FROM colors GROUP BY color ORDER BY COUNT(items) DESC
    By using a named column in jesteps example the query becomes a bit easier to read and you are reusing your count(items) code so it becomes a bit more maintenance friendly.
     
    chisara, Apr 30, 2009 IP
    timallard likes this.
  6. timallard

    timallard Well-Known Member

    Messages:
    1,634
    Likes Received:
    62
    Best Answers:
    0
    Trophy Points:
    158
    #6
    Thanks everyone, this was helpful.
     
    timallard, May 1, 2009 IP