Re: sort for ranking - Mailing list pgsql-sql
| From | Henshall, Stuart - TNP Southwest |
|---|---|
| Subject | Re: sort for ranking |
| Date | |
| Msg-id | E382B5D8EDE1D6118DBE0008C759BCD601EAAC4F@WCPEXCHANGE Whole thread Raw |
| In response to | sort for ranking (Andreas Schmitz <a.schmitz@cityweb.de>) |
| List | pgsql-sql |
<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 />