Message-ID: <3F09AB9C.DA259352@nsd.ca>
Date: Mon, 07 Jul 2003 13:19:24 -0400
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: a.schmitz@cityweb.de
Cc: pgsql-sql@postgresql.org
Subject: Re: sort for ranking
References: <200307071614.14913.a.schmitz@cityweb.de>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Andreas,

try 

select sum_user,nextval('tipp_eval_seq')-1 as ranking from (
 select user_sum from tbl_sums order by user_sum desc) as ss;


JLL


Andreas Schmitz wrote:
> 
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match