Thread
-
Fwd: Silly question about numbering of rows?
Ken Corey <ken.corey@atomic-interactive.com> — 2001-03-13T10:57:54Z
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 -
Re: Fwd: Silly question about numbering of rows?
Brett W. McCoy <bmccoy@chapelperilous.net> — 2001-03-13T14:26:22Z
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
-
Re: Fwd: Silly question about numbering of rows?
Ken Corey <ken.corey@atomic-interactive.com> — 2001-03-13T14:55:54Z
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
-
Re: Fwd: Silly question about numbering of rows?
Ken Corey <ken.corey@atomic-interactive.com> — 2001-03-13T15:55:48Z
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
-
Re: Fwd: Silly question about numbering of rows?
Andrew McMillan <andrew@catalyst.net.nz> — 2001-03-14T05:32:33Z
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 -
Re: Re: Fwd: Silly question about numbering of rows?
Anthony E . Greene <agreene@pobox.com> — 2001-03-14T07:05:24Z
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/> -
Re: question about numbering of rows?
Vijay Deval <deval@giaspn01.vsnl.net.in> — 2001-03-14T16:08:19Z
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