Обсуждение: Problem with getting the right order

I have a datbase that look like this:

vnr|cnr|enr|             ip|dato
---+---+---+---------------+----------------------
  1|  1| 17|195.192.136.223|1998-10-14 02:30:18+02
  2|  1|  1|195.192.136.164|1998-10-14 02:33:03+02
  5|  1| 13|195.192.136.173|1998-10-14 02:49:24+02
  8|  1| 15|195.192.136.207|1998-10-14 02:49:27+02
  9|  1| 16|195.192.136.211|1998-10-14 02:49:31+02
 10|  1| 11|195.192.136.123|1998-10-14 02:49:44+02
 11|  1| 15|195.192.136.162|1998-10-14 02:49:48+02
  7|  1| 13|195.192.136.126|1998-10-14 02:49:50+02


Where vnr, cnr, enr are int fields, and ip, date are text. The database
is used for a votingsystem. vnr is the vote number, cnr is the
contestnumber and enr is the entrynumber.

When i do a :

./psql -c "select count(vnr),enr from votes where cnr=1 group by enr"
vote

to get the result from contest number 1 i get :

count|enr
-----+---
   11|  1
    1|  2
    1|  3
    4|  4
    1|  5
    1|  6
   10|  8
....

This is almost what i want, execpt that i want the result to be ordered
by count, instead og enr. so that the entry, which got most  votes is listed first

For example:

   11|  1
   10|  8
    4|  4
    1|  2
    1|  3
    1|  5
    1|  6



hope someone outthere can help me :)

Henrik Pedersen
Demark

On Wed, 21 Oct 1998, Henrik Pedersen wrote:

// When i do a :
//
// ./psql -c "select count(vnr),enr from votes where cnr=1 group by enr"
// vote

// This is almost what i want, execpt that i want the result to be
// ordered by count, instead og enr. so that the entry, which got most
// votes is listed first

    You should slap an ``order by count'' at the end.  I think it'd be
``order by count(vnr)'' or you could alias it and order it by the alias.

// For example:
//
//    11|  1
//    10|  8
//     4|  4
//     1|  2
//     1|  3
//     1|  5
//     1|  6
//
//
//
// hope someone outthere can help me :)
//
// Henrik Pedersen
// Demark
//
//

--
SA, beyond.com                            The world is watching America,
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L________________________________________ and America is watching TV. __

Generally, something like

       select count(vnr), enr
       from votes
       group by err
       order by 1 desc

should work. But I tried it only with my own tables ... For
referencing to the first column of the resulting table ("order by 1")
you can also use

       select count(vnr) as the_count, [...]
       order by the_count desc

If you want to get reversed ordering (you said you'd like to get the
bigger numbers first), you have to specify "desc", default is "asc",
which will do it the other way round.

Hope it helps!
Ulf

--
======================================================================
 %%%%%            Ulf Mehlig              <ulf.mehlig@uni-bremen.de>
   %%%%!%%%       Projekt "MADAM"         <umehlig@uni-bremen.de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~