From: scott.marlowe <scott.marlowe@ihs.com>
To: Andreas Schmitz <a.schmitz@cityweb.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: sort for ranking
Date: Mon, 7 Jul 2003 13:51:29 -0600 (MDT)
Message-ID: <Pine.LNX.4.33.0307071350300.4823-100000@css120.ihs.com> (raw)
In-Reply-To: <200307071614.14913.a.schmitz@cityweb.de>
I'm gonna guess you stored your ranking as a "text" field, but now you'd
like to treat it like an int / numeric.
While it would be better to go ahead and convert it, you can always cast
it:
select * from table order by textfield::int;
On Mon, 7 Jul 2003, 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
>
>
>
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: scott.marlowe@ihs.com, a.schmitz@cityweb.de
Subject: Re: sort for ranking
In-Reply-To: <Pine.LNX.4.33.0307071350300.4823-100000@css120.ihs.com>
* 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