Re: sort for ranking

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(dot)schmitz(at)cityweb(dot)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