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@catalyst.net.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