Skip site navigation (1) Skip section navigation (2)

Re: Fwd: Silly question about numbering of rows?

From: Ken Corey <ken(dot)corey(at)atomic-interactive(dot)com>
To: "Brett W(dot) McCoy" <bmccoy(at)chapelperilous(dot)net>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Fwd: Silly question about numbering of rows?
Date: 2001-03-13 14:55:54
Message-ID: 0103131455541A.06836@kenlinux.bithub.org (view raw or flat)
Thread:
Lists: pgsql-novice
On Tuesday 13 March 2001  2:26 pm, Brett W. McCoy wrote:
> Not a moderated list.

Ah well, sorry for the duplicates.

Thanks for the answer, though!

> >         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
>
> The potential for duplicate records is rampant here.  What is the primary
> key?  Do these values use foreign keys?

The primary key is id/make/model/year, sorted by sum(value).

Doh! I think I forgot to mention a critical part: selects on the table vary 
model and year to be either '0' (which represents 'all') or the specific 
number concerned.

So, there are 4 ways to slice this data: 
	a particular make, all models, all years
	a particular make, particular model, all years
	a particular make, all model, particular years
	a particular make, particular model, particular years

> > 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.
>
> Why not?  The serial number can be used exclusively for identifying a
> unique row.  That's definitely the easiest way to do this numbering.
>
> > 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.
>
> Ah, that is a problem.

Because different rows get used depending on which 'slice' you use (as 
defined above).

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

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2001-03-13 15:37:02
Subject: Re: FW: look-up cache failures
Previous:From: Dax DuskinDate: 2001-03-13 14:34:21
Subject: perl installation problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group