Date: Mon, 7 Jul 2003 13:51:29 -0600 (MDT)
From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Andreas Schmitz <a.schmitz@cityweb.de>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: sort for ranking
In-Reply-To: <200307071614.14913.a.schmitz@cityweb.de>
Message-ID: <Pine.LNX.4.33.0307071350300.4823-100000@css120.ihs.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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
> 
> 
>