From: Jean-Luc Lachance <jllachan@nsd.ca>
To: a.schmitz@cityweb.de
Cc: pgsql-sql@postgresql.org
Subject: Re: sort for ranking
Date: Mon, 07 Jul 2003 13:19:24 -0400
Message-ID: <3F09AB9C.DA259352@nsd.ca> (raw)
References: <200307071614.14913.a.schmitz@cityweb.de>
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
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: jllachan@nsd.ca, a.schmitz@cityweb.de
Subject: Re: sort for ranking
In-Reply-To: <3F09AB9C.DA259352@nsd.ca>
* 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