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
~~~~~~~~~~~~~~~~~~~~