SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    count and order by

    hi,

    i've managed to sort out some sql to count duplicate entries in one field but now want to order the results by the count

    this is my sql

    SELECT DISTINCT ballot.selection, Games.GameName, ( SELECT COUNT(*) FROM ballot WHERE ballot.selection = Games.GameID ) AS num
    FROM ballot,Games
    WHERE ballot.selection = Games.GameID


    i would like to order the results by the num

    however when i try to do this it clashes with the distinct


    can anyone help with either an Order By solution or maybe a more efficient sql


    thanks
    ian

  2. #2
    Xbox why have you forsaken me? moospot's Avatar
    Join Date
    Feb 2001
    Location
    Clearwater, FL
    Posts
    3,615
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try GROUP BY num ORDER BY num

  3. #3
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi
    thanks

    tried this and still the 'order by' clashes with the 'distinct'

    no worries - client now decided against this feature


    cheers
    ian

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you should have pasted this in the db section.

    Or called Rudy to help.

    You cant use group by with the distinct, group by produces an output row.

    the sort columns must appear in the select list, when using distinct

    Did not try this but you can try

    SELECT DISTINCT ballot.selection, Games.GameName, ( SELECT COUNT(*) FROM ballot WHERE ballot.selection = Games.GameID ) AS num
    FROM ballot,Games
    WHERE ballot.selection = Games.GameID
    order by selection, gamename, num desc--or asc

  5. #5
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think all the coding sections need a sticky thread saying something like "SQL Question? Go to Databases!!"


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
  •