Ordering a GROUP BY query
Question: I'm using sqlite3 database in my very simple program written in python. Table
I have a problem with contains three columns:

    
    
    id, software_version, place
    

Now I would like to count the frequency of a particular software version, so I
created this query:

    
    
    SELECT software_ssh, COUNT (software_ssh)  FROM simple_table group by software_ssh
    

As a result I have got:

    
    
    program_version_1.1, 2
    program_version_1.2, 2
    program_version_1.3, 20
    program_version_2.1, 7
    

Unfortunately the output is not sorted by frequency. How can I fix the query
to make it work better?


Answer: Add an `ORDER BY` on the `COUNT` results; give that result an alias:

    
    
    SELECT
        software_ssh,
        COUNT (software_ssh) as freq
    FROM simple_table
    GROUP BY software_ssh 
    ORDER BY freq
    

Use `ORDER BY freq DESC` if you need the rows ordered from most frequent to
least.

Demo:

    
    
    >>> from pprint import pprint
    >>> c.execute('SELECT software_ssh, COUNT (software_ssh)  FROM simple_table group by software_ssh')
    <sqlite3.Cursor object at 0x102034490>
    >>> pprint(list(c))
    [(u'program_version_1.1', 2),
     (u'program_version_1.2', 2),
     (u'program_version_1.3', 20),
     (u'program_version_2.1', 7)]
    >>> c.execute('''
    ... SELECT
    ...     software_ssh,
    ...     COUNT (software_ssh) as freq
    ... FROM simple_table
    ... GROUP BY software_ssh
    ... ORDER BY freq
    ... ''')
    <sqlite3.Cursor object at 0x102034490>
    >>> pprint(list(c))
    [(u'program_version_1.1', 2),
     (u'program_version_1.2', 2),
     (u'program_version_2.1', 7),
     (u'program_version_1.3', 20)]
    

