From: Andreas Schmitz <a.schmitz@cityweb.de>
To: pgsql-sql@postgresql.org
Subject: sort for ranking
Date: Mon, 7 Jul 2003 16:14:14 +0200
Message-ID: <200307071614.14913.a.schmitz@cityweb.de> (raw)
Hello *,
I have a little problem that confuses me. We are gathering values from a table
as a sum to insert them into another table. I also need to get a ranking at
insert (i.e. Highest points will get first place and so on). I tried ton
invole a sequence to qualify the ranking by select at insert.
So I tried the following (smaller example)
select setval('tipp_eval_seq',1);
select sum_user,nextval('tipp_eval_seq')-1 as ranking from tbl_sums order by
ranking desc, user_sum asc;
user_sum | ranking
----------+---------
46 | 30
45 | 26
44 | 28
43 | 25
42 | 1
41 | 2
39 | 3
38 | 27
36 | 19
35 | 18
34 | 20
31 | 24
30 | 17
29 | 15
28 | 16
27 | 12
26 | 11
25 | 23
24 | 21
23 | 10
19 | 13
16 | 9
12 | 7
11 | 8
10 | 29
8 | 6
7 | 5
6 | 14
2 | 4
1 | 22
(30 rows)
As you can see, the sums are sorted correctly but the ranking is a mess. I
recongnized that the select seems to follow primarily the internal table
order. Is there any way to solve this nicely. Hints and solutions are
appreciated.
Thanks in advance
-Andreas
--
Andreas Schmitz - Phone +49 201 8501 318
Cityweb-Technik-Service-Gesellschaft mbH
Friedrichstr. 12 - Fax +49 201 8501 104
45128 Essen - email a.schmitz@cityweb.de
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: a.schmitz@cityweb.de
Subject: Re: sort for ranking
In-Reply-To: <200307071614.14913.a.schmitz@cityweb.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox