Re: Fwd: Silly question about numbering of rows?

Ken Corey wrote:
>
> 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...

So what is 'numbering magic' supposed to do? Return 1 for the 1st row,
2 for the second, etc, just for this particular query, as of right now?

If so, what value is it? What use is it within your application? I ask
because it seems like a piece of non-data. Tautological, almost, that
makes me wonder if you are making a different design mistake somewhere
else...

Also, it might help to know what language your application is being
written in.

I regularly construct queries as strings, based on variables, and then
process the rows one at a time (using PHP or Perl mainly) and in a
programming language it is trivial to use a loop counter to display a
line number in the output.

Regards,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew(at)catalyst(dot)net(dot)nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267