pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Silly question about numbering of rows?
3+ messages / 2 participants
[nested] [flat]

* Silly question about numbering of rows?
@ 2001-03-13 08:12 Ken Corey <ken.corey@atomic-interactive.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Ken Corey @ 2001-03-13 08:12 UTC (permalink / raw)
  To: 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.corey@atomic-interactive.com



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Fwd: Silly question about numbering of rows?
@ 2001-03-13 10:55 Ken Corey <ken.corey@atomic-interactive.com>
  2001-03-13 19:44 ` Re: Fwd: Silly question about numbering of rows? Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Ken Corey @ 2001-03-13 10:55 UTC (permalink / raw)
  To: 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.corey@atomic-interactive.com



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Fwd: Silly question about numbering of rows?
  2001-03-13 10:55 Fwd: Silly question about numbering of rows? Ken Corey <ken.corey@atomic-interactive.com>
@ 2001-03-13 19:44 ` Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Peter Eisentraut @ 2001-03-13 19:44 UTC (permalink / raw)
  To: Ken Corey <ken.corey@atomic-interactive.com>; +Cc: pgsql-sql

Ken Corey writes:

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

The rows are automatically numbered.  In libpq, you'd use
PQgetvalue(result, rownumber, fieldnumber).  Other interfaces have similar
functions.  If the interface has a "fetch"-like approach, it seems trivial
to keep a counter.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-03-13 19:44 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-13 08:12 Silly question about numbering of rows? Ken Corey <ken.corey@atomic-interactive.com>
2001-03-13 10:55 Fwd: Silly question about numbering of rows? Ken Corey <ken.corey@atomic-interactive.com>
2001-03-13 19:44 ` Peter Eisentraut <peter_e@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox