Обсуждение: Fwd: Silly question about numbering of rows?
Moderator, please don't approve the other two posts I've made...just this one
is plenty...;^)
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
-------------------------------------------------------
--
Ken Corey, CTO Atomic Interactive, Ltd. ken.corey@atomic-interactive.com
On Tue, 13 Mar 2001, Ken Corey wrote:
> Moderator, please don't approve the other two posts I've made...just this one
> is plenty...;^)
Not a moderated list.
> 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
The potential for duplicate records is rampant here. What is the primary
key? Do these values use foreign keys?
> 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.
> It sounds easiest to me to just punt and number the rows as they are returned
> in my calling application...
That might be the way to go, if the ordering and numbering is changing
constantly.
-- Brett
http://www.chapelperilous.net/~bmccoy/
------------------------------------------------------------------------
I don't mind what Congress does, as long as they don't do it in the
streets and frighten the horses.
-- Victor Hugo
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.corey@atomic-interactive.com
On Tuesday 13 March 2001 3:56 pm, you wrote: > Have you missed one and after "make=2" ? Hi Vijay...yes, of course, you're right. I'd like to say it's a 'cut and paste' error and blame the computer...but I'm afraid I typed it in by hand. (The question still stands, though...) > > Vijay > > Ken Corey wrote: > > I have a table like this: > > > > id | make | model | year | value > > ---------+-----------+--------+-------+------- > > 57 | 2 | 0 | 4 | 4750 > > 57 | 2 | 3 | 4 | 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; -- Ken Corey, CTO Atomic Interactive, Ltd. ken.corey@atomic-interactive.com
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
On Tue, 13 Mar 2001 09:55:54 Ken Corey wrote:
>> > 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
It seems to me that you are misusing the WHERE clause in your queries. Why
not just select like this:
SELECT *
FROM tblname
WHERE make='$make';
SELECT *
FROM tblname
WHERE make='$make' AND model='$model';
SELECT *
FROM tblname
WHERE make='$make'
AND YEAR >= '$lowyear' AND YEAR <= '$highyear';
SELECT *
FROM tblname
WHERE make='$make'
AND model='$model'
AND YEAR >= '$lowyear' AND YEAR <= '$highyear';
That way you don't need a zero in your column values to simulate 'all'. This
will ensure each row actually represents an automobile and not some
artificial entry created for use in queries.
Your application logic will only be a little more complicated, and your
queries will be a lot faster, especially as your table size grows. In Perl,
I'd do something like this:
$sql = 'SELECT * FROM tblname ';
if ($make > 0 && $model == 0 && $year == 0) {
$sql .= "WHERE make='$make'";
} elsif ($make > 0 && $model > 0 && $year == 0) {
$sql .= "WHERE make='$make' AND model='$model'";
} elsif ($make > 0 && $model == 0 && $year > 0) {
$sql .= "WHERE make='$make' AND year='$year'";
} elsif ($make > 0 && $model > 0 && $year > 0) {
$sql .= "WHERE make='$make' AND model='$model' AND year='$year'";
} else {
# Some error-handling code here
}
Tony
--
Anthony E. Greene <agreene@pobox.com> <http://www.pobox.com/~agreene/>
PGP Key: 0x6C94239D/7B3D BD7D 7D91 1B44 BA26 C484 A42A 60DD 6C94 239D
Chat: AOL/Yahoo: TonyG05 ICQ: 91183266
Linux. The choice of a GNU Generation. <http://www.linux.org/>
Dear Ken
I just tried this out.
psql test
{return prompts}
\! tcl query
And it does work. No need to change over to tcl. Just get little bit of help
when convenient.
Vijay
Ken Corey wrote:
> On Tuesday 13 March 2001 3:56 pm, you wrote:
> > Have you missed one and after "make=2" ?
>
> Hi Vijay...yes, of course, you're right. I'd like to say it's a 'cut and
> paste' error and blame the computer...but I'm afraid I typed it in by hand.
>
> (The question still stands, though...)
>
> >
> > Vijay
> >
> > Ken Corey wrote:
> > > I have a table like this:
> > >
> > > id | make | model | year | value
> > > ---------+-----------+--------+-------+-------
> > > 57 | 2 | 0 | 4 | 4750
> > > 57 | 2 | 3 | 4 | 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;
>
> --
> Ken Corey, CTO Atomic Interactive, Ltd. ken.corey@atomic-interactive.com