Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2DEdix27877 for ; Tue, 13 Mar 2001 09:39:44 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from atomic-interactive.com ([195.167.153.42]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2D8JSx78248 for ; Tue, 13 Mar 2001 03:19:29 -0500 (EST) (envelope-from ken.corey@atomic-interactive.com) Received: (qmail 24086 invoked from network); 13 Mar 2001 08:13:50 -0000 Received: from unknown (HELO kenlinux.bithub.org) (192.168.202.201) by smokey.bithub.org with SMTP; 13 Mar 2001 08:13:50 -0000 From: Ken Corey Reply-To: ken.corey@atomic-interactive.com Organization: Atomic Interactive, Ltd. To: Subject: Silly question about numbering of rows? Date: Tue, 13 Mar 2001 08:12:34 +0000 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="iso-8859-1" MIME-Version: 1.0 Message-Id: <0103130812340T.06836@kenlinux.bithub.org> Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id f2D8Jwx78269 X-Archive-Number: 200103/257 X-Sequence-Number: 1916 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 , 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.corey@atomic-interactive.com