Thread: 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
--
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
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
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
>
>
>
<p><font size="2">Could you do something like the following:</font><br /><font size="2">SELECT sum_user,(SELECT
count(sum_user)+1FROM tbl_sums AS t WHERE t.sum_user>tbl_sums.sum_user) AS ranking FROM tbl_sums ORDER BY
ranking</font><p><fontsize="2">hth,</font><br /><font size="2">- Stuart</font><br /><font size="2">P.S. Sorry about
formatchange, the disclaimer adder forces it :(</font><p><font size="2">> -----Original Message-----</font><br
/><fontsize="2">> From: Andreas Schmitz [<a
href="mailto:a.schmitz@cityweb.de">mailto:a.schmitz@cityweb.de</a>]</font><br/><font size="2">> Sent: 07 July 2003
15:14</font><br/><font size="2">> To: pgsql-sql@postgresql.org</font><br /><font size="2">> Subject: [SQL] sort
forranking</font><br /><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">> </font><br
/><fontsize="2">> Hello *,</font><br /><font size="2">> </font><br /><font size="2">> I have a little problem
thatconfuses me. We are gathering </font><br /><font size="2">> values from a table </font><br /><font size="2">>
asa sum to insert them into another table. I also need to </font><br /><font size="2">> get a ranking at </font><br
/><fontsize="2">> insert (i.e. Highest points will get first place and so on). </font><br /><font size="2">> I
triedton </font><br /><font size="2">> invole a sequence to qualify the ranking by select at insert.</font><br
/><fontsize="2">> </font><br /><font size="2">> So I tried the following (smaller example)</font><br /><font
size="2">></font><br /><font size="2">> select setval('tipp_eval_seq',1);</font><br /><font size="2">> select
sum_user,nextval('tipp_eval_seq')-1as ranking from </font><br /><font size="2">> tbl_sums order by </font><br
/><fontsize="2">> ranking desc, user_sum asc;</font><br /><font size="2">> </font><br /><font size="2">>
user_sum| ranking </font><br /><font size="2">> ----------+---------</font><br /><font size="2">> 46
| 30</font><br /><font size="2">> 45 | 26</font><br /><font size="2">> 44 |
28</font><br/><font size="2">> 43 | 25</font><br /><font size="2">> 42 | 1</font><br
/><fontsize="2">> 41 | 2</font><br /><font size="2">> 39 | 3</font><br /><font
size="2">> 38 | 27</font><br /><font size="2">> 36 | 19</font><br /><font
size="2">> 35 | 18</font><br /><font size="2">> 34 | 20</font><br /><font
size="2">> 31 | 24</font><br /><font size="2">> 30 | 17</font><br /><font
size="2">> 29 | 15</font><br /><font size="2">> 28 | 16</font><br /><font
size="2">> 27 | 12</font><br /><font size="2">> 26 | 11</font><br /><font
size="2">> 25 | 23</font><br /><font size="2">> 24 | 21</font><br /><font
size="2">> 23 | 10</font><br /><font size="2">> 19 | 13</font><br /><font
size="2">> 16 | 9</font><br /><font size="2">> 12 | 7</font><br /><font
size="2">> 11 | 8</font><br /><font size="2">> 10 | 29</font><br /><font
size="2">> 8 | 6</font><br /><font size="2">> 7 | 5</font><br /><font
size="2">> 6 | 14</font><br /><font size="2">> 2 | 4</font><br /><font
size="2">> 1 | 22</font><br /><font size="2">> (30 rows)</font><br /><font size="2">> </font><br
/><fontsize="2">> </font><br /><font size="2">> As you can see, the sums are sorted correctly but the ranking
</font><br/><font size="2">> is a mess. I </font><br /><font size="2">> recongnized that the select seems to
followprimarily the </font><br /><font size="2">> internal table </font><br /><font size="2">> order. Is there
anyway to solve this nicely. Hints and solutions are </font><br /><font size="2">> appreciated.</font><br /><font
size="2">></font><br /><font size="2">> Thanks in advance </font><br /><font size="2">> </font><br /><font
size="2">>-Andreas</font><br /><font size="2">> </font><br /><font size="2">> </font><br /><p><b><i><font
size="2">DISCLAIMER:</font></i><fontsize="2">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
notthe intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by
youin reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received
thismessage in error. Thank you.</font></b><br />