Thread

sort for ranking

Andreas Schmitz <a.schmitz@cityweb.de> — 2003-07-07T14:14:14Z

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

Re: sort for ranking

Jean-Luc Lachance <jllachan@nsd.ca> — 2003-07-07T17:19:24Z

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

Re: sort for ranking

scott.marlowe <scott.marlowe@ihs.com> — 2003-07-07T19:51:29Z

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