Content-Type: text/plain;
  charset="us-ascii"
From: Andreas Schmitz <a.schmitz@cityweb.de>
Reply-To: a.schmitz@cityweb.de
Organization: Cityweb Network
To: pgsql-sql@postgresql.org
Subject: sort for ranking
Date: Mon, 7 Jul 2003 16:14:14 +0200
User-Agent: KMail/1.4.1
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200307071614.14913.a.schmitz@cityweb.de>


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