Fwd: Silly question about numbering of rows?

From: Ken Corey <ken(dot)corey(at)atomic-interactive(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Fwd: Silly question about numbering of rows?
Date: 2001-03-13 10:55:47
Message-ID: 01031310554712.06836@kenlinux.bithub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi All,

I haven't posted a silly question in a while, and didn't want to break my
streak...

I have a table like this:

id | make | model | year | value
---------+-----------+--------+-------+-------
57 | 2 | 0 | 4 | 4750
57 | 2 | 3 | 4 | 4750
57 | 2 | 0 | 0 | 4750
57 | 2 | 0 | 3 | 4750
57 | 2 | 3 | 0 | 4750
57 | 2 | 3 | 3 | 4750
2 | 2 | 0 | 3 | 4750
2 | 2 | 3 | 3 | 4750
2 | 2 | 0 | 4 | 4350
2 | 2 | 3 | 4 | 4350
2 | 2 | 0 | 0 | 4750
2 | 2 | 0 | 5 | 4750
2 | 2 | 3 | 0 | 4750
2 | 2 | 3 | 5 | 4750

I want to be able to select from this table like this:

select
<numbering magic>, id, value
from
mytable
where
make=2
model=3
and year=5
order by score desc;

which would return this:
nbring | id | value
-------+---------+-------
1 | 57 | 4750
2 | 2 | 4350

Everything is working swimmingly, except for the numbering magic. As you can
see there are several variations of data in that table, so I can't use a
serial on the table directly.

Further, this table is likely to change on a minute by minute basis, so I
don't want to create a permanent numbering that will just have to be changed.

I thought about creating a temporary table with a serial and selecting into
that so that a numbering is created and then returning rows from that, but
that sounds like much busywork for the database.

It sounds easiest to me to just punt and number the rows as they are returned
in my calling application...

What's the best approach here?

--
Ken Corey, CTO Atomic Interactive, Ltd. ken(dot)corey(at)atomic-interactive(dot)com

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Christopher Sawtell 2001-03-13 11:53:05 my pgsql error?
Previous Message Richard Huxton 2001-03-13 08:22:33 Re: Poor document!!