Mailing List Archive


Back to the month index Back to the list index
Art Matheny (matheny@usf.edu)
Mon, 17 Nov 1997 17:03:08 -0500 (EST) 

Date: Mon, 17 Nov 1997 17:03:08 -0500 (EST)
From: Art Matheny <matheny@usf.edu>
Subject: Re: [msqlperl] ordering rows
Message-ID: <Pine.GSO.3.96.971117164939.5209C-100000@curiac>

On Fri, 14 Nov 1997, Grant Totten wrote:

> Hello all,
>
> I've got a bit of a dilemma. Here is how I've defined
> a table:
>
> [grantt]> relshow comments
>
> Database = sde
> Table = comments
>
> +-----------------+----------+--------+----------+--------------+
> | Field | Type | Length | Not Null | Unique Index |
> +-----------------+----------+--------+----------+--------------+
> | updt | char | 14 | N | N/A |
> | comment | text | 132 | N | N/A |
> | comments_updt | index | N/A | N/A | N |
> +-----------------+----------+--------+----------+--------------+
>
> The idea is that there can be multiple 'comment' rows per 'updt'.
> For example:
>
> [grantt_side]> sql "select * from comments where updt = 'grantt.240'"
> mSQL >
> Query OK. 7 row(s) modified or retrieved.
>
> +----------------+--------------------------------------------------------------------------------------------------------------------------------------+
> | updt | comment |
> +----------------+--------------------------------------------------------------------------------------------------------------------------------------+
> | grantt.240 | to support -t (time mode) and -b (build mode) |
> | grantt.240 | |
> | grantt.240 | a valid context. |
> | grantt.240 | Also disallowing a user to select a branch-point as |
> | grantt.240 | |
> | grantt.240 | active and the admin VOB is mounted. |
> | grantt.240 | as well as adding a check to see if the sdeadmin view is |
> +----------------+--------------------------------------------------------------------------------------------------------------------------------------+
>
>
> So far, to get the rows to come out in the right order, I've
> been using "order by _rowid". It's been working OK for a while.
>
> But, that method of ordering doesn't necesarily work if you delete and
> re-insert rows. It seems that the row IDs are re-used so you end up
> with a semi-random ordering if you use _rowid.
>
> So, what should I do to ensure I get the rows to come out in the
> right order? Do I have to include a 'line number'? I've looked into
> the _seq variable and it doesn't seem to be the right choice here.
> Or is it?
>
> Any recommendations?
>
> Thanks in advance,
>
> Grant Totten
> Nortel Public Data Networks
>
>

I would say it depends on how much editing you need to do on these comment
records. The _seq variable would work only if you never have to insert
new lines ahead of existing ones. If you decide to insert a new line in
the middle of an existing comment sequence, the _seq value of the new line
would be greater than the existing lines and would therefore appear as the
final line.

It seems to me that a line_number field would be the more flexible method. 

--
-- Art Matheny          LIB 612       813-974-1795      FAX: 813-974-1799
-- Academic Computing   University of South Florida     Tampa, FL 33620
-- matheny@usf.edu      http://curiac.acomp.usf.edu/nic/