On May 4, 2005, at 20:50, Randal L. Schwartz wrote:
Well, yes. I was (falsely?) recalling that there was a pure SQL way
to do this though.
Here's a pure SQL method. There might be more performant ways of
rewriting the query, but this should do what you want.
test=# create table persons (
per
> "Harald" == Harald Fuchs <[EMAIL PROTECTED]> writes:
Harald> Using a temporary sequence for that would avoid naming conflicts.
>> P.S. I'm sure you can wrap it in plperl stored procedure :)
Well, yes. I was (falsely?) recalling that there was a pure SQL way
to do this though.
And the poi
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Well, if you don't need the ranks to be sequential, merely ordered:
CREATE TABLE ranker (id INT, age INT);
...
SELECT b.w-a.age AS rank, a.id, a.age
FROM (SELECT * FROM ranker ORDER BY age DESC, id) AS a,
(SELECT max(age)+1 AS w FROM ranker) as b;
In article <[EMAIL PROTECTED]>,
Lyubomir Petrov <[EMAIL PROTECTED]> writes:
> create sequence seq_tmp;
> select nextval('seq_tmp') as rank, a.id, a.name from (select id, name
> from t order by name desc) a;
> drop sequence seq_tmp;
Using a temporary sequence for that would avoid naming conflicts.
> > If I order a query by ascending age, the youngest person gets
> > rank 1, the second youngest gets rank 2, the third youngest gets rank 3,
> > and if the fourth and fifth tie, they both get 4, and the next one gets 6.
> >
> > You know, rank? :)
>
> You could use a plPerl function.
To do it w
If I order a query by ascending age, the youngest person gets
rank 1, the second youngest gets rank 2, the third youngest gets rank 3,
and if the fourth and fifth tie, they both get 4, and the next one gets 6.
You know, rank? :)
You could use a plPerl function.
Sincerely,
Joshua D. Drake
u get the idea. This
would probably need to be cleaned up a bit, but I think would do something
like what you need.
Sean
- Original Message -
From: "Lyubomir Petrov" <[EMAIL PROTECTED]>
To: "Randal L. Schwartz"
Cc:
Sent: Tuesday, May 03, 2005 9:13 PM
Subject: Re: [GEN
Randal L. Schwartz wrote:
I'm probably asking a FAQ, but a few google searches didn't seem
to point me in the right place.
Is there a simple way with PostgreSQL to assign relative ranks to the
result of a query ORDER BY? That is, I want to either have a view
that cheaply assigns the ranks, or be a
> "Matthew" == Matthew Terenzio <[EMAIL PROTECTED]> writes:
Matthew> On May 3, 2005, at 8:30 PM, Randal L. Schwartz wrote:
>> Is there a simple way with PostgreSQL to assign relative ranks to the
>> result of a query ORDER BY?
Matthew> What do you mean by ranks?
If I order a query by ascen
On May 3, 2005, at 8:30 PM, Randal L. Schwartz wrote:
Is there a simple way with PostgreSQL to assign relative ranks to the
result of a query ORDER BY?
What do you mean by ranks?
---(end of broadcast)---
TIP 9: the planner will ignore your desire to
I'm probably asking a FAQ, but a few google searches didn't seem
to point me in the right place.
Is there a simple way with PostgreSQL to assign relative ranks to the
result of a query ORDER BY? That is, I want to either have a view
that cheaply assigns the ranks, or be able to update a column w
11 matches
Mail list logo
