pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsort for ranking
4+ messages / 4 participants
[nested] [flat]
* sort for ranking
@ 2003-07-07 14:14 Andreas Schmitz <a.schmitz@cityweb.de>
2003-07-07 17:19 ` Re: sort for ranking Jean-Luc Lachance <jllachan@nsd.ca>
2003-07-07 19:51 ` Re: sort for ranking scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 2 replies; 4+ messages in thread
From: Andreas Schmitz @ 2003-07-07 14:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sort for ranking
2003-07-07 14:14 sort for ranking Andreas Schmitz <a.schmitz@cityweb.de>
@ 2003-07-07 17:19 ` Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 4+ messages in thread
From: Jean-Luc Lachance @ 2003-07-07 17:19 UTC (permalink / raw)
To: a.schmitz@cityweb.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sort for ranking
2003-07-07 14:14 sort for ranking Andreas Schmitz <a.schmitz@cityweb.de>
@ 2003-07-07 19:51 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 4+ messages in thread
From: scott.marlowe @ 2003-07-07 19:51 UTC (permalink / raw)
To: Andreas Schmitz <a.schmitz@cityweb.de>; +Cc: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: sort for ranking
@ 2003-07-07 15:06 Henshall, Stuart - TNP Southwest <shenshall@tnp-southwest.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Henshall, Stuart - TNP Southwest @ 2003-07-07 15:06 UTC (permalink / raw)
To: 'a.schmitz@cityweb.de' <a.schmitz@cityweb.de>; pgsql-sql
Could you do something like the following:
SELECT sum_user,(SELECT count(sum_user)+1 FROM tbl_sums AS t WHERE
t.sum_user>tbl_sums.sum_user) AS ranking FROM tbl_sums ORDER BY ranking
hth,
- Stuart
P.S. Sorry about format change, the disclaimer adder forces it :(
> -----Original Message-----
> From: Andreas Schmitz [mailto:a.schmitz@cityweb.de]
> Sent: 07 July 2003 15:14
> To: pgsql-sql@postgresql.org
> Subject: [SQL] 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
>
>
DISCLAIMER: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 not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-07-07 19:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-07 14:14 sort for ranking Andreas Schmitz <a.schmitz@cityweb.de>
2003-07-07 17:19 ` Jean-Luc Lachance <jllachan@nsd.ca>
2003-07-07 19:51 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-07 15:06 Re: sort for ranking Henshall, Stuart - TNP Southwest <shenshall@tnp-southwest.co.uk>
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