Re: sort for ranking

Could you do something like the following:
SELECT sum_user,(SELECT count(sum_user)+1 FROM tbl_sums AS t WHERE
t.sum_user>tbl_sums.sum_user) AS ranking FROM tbl_sums ORDER BY ranking
hth,
- Stuart
P.S. Sorry about format change, the disclaimer adder forces it :(

> -----Original Message-----
> From: Andreas Schmitz [mailto:a(dot)schmitz(at)cityweb(dot)de]
> Sent: 07 July 2003 15:14
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] sort for ranking
>
>
>
> 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
>
>

DISCLAIMER:The information in this message is confidential and may be
legally privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.