Sorting String Values

[John Smith]

Hello all:

New problem. I am currently storing some mostly numeric values as ids
in a db but with the possiblity that there are revisions to each. For
instance say we have a record such as 100, a revision may need to be
added such as 100a. As a result this column in the db must be set to a
string data type. However, when I do so the sort order gets whacked.
We have some records in the hundreds and they show up after those in the
thousands as a result of string sorting. For example, record 500 is
less than 1000 and so shows up below. However, we need it to sort as
though it were sorting integers but with the added requirement of
allowing for the revisions. Is this possible or should I come up with a
different scheme as far as revisions go such as tack on the revision tag
later in processing and not store it in the db (this is undesirable).

If you made it this far, thanks for reading :),

John

[John C]

RE: Sorting String Values

If it works for the rest of the application you could always store a fixed
number of digits.

For example, always store 6 digits so you would have 000100, 000500, 001000

Then on a change 000100a would not change the sort order.

John

"John Smith" wrote:
[color=blue]
> Hello all:
>
> New problem. I am currently storing some mostly numeric values as ids
> in a db but with the possiblity that there are revisions to each. For
> instance say we have a record such as 100, a revision may need to be
> added such as 100a. As a result this column in the db must be set to a
> string data type. However, when I do so the sort order gets whacked.
> We have some records in the hundreds and they show up after those in the
> thousands as a result of string sorting. For example, record 500 is
> less than 1000 and so shows up below. However, we need it to sort as
> though it were sorting integers but with the added requirement of
> allowing for the revisions. Is this possible or should I come up with a
> different scheme as far as revisions go such as tack on the revision tag
> later in processing and not store it in the db (this is undesirable).
>
> If you made it this far, thanks for reading :),
>
> John
>[/color]

[Bjorn Abelli]

Re: Sorting String Values


"John Smith" wrote...
[color=blue]
> New problem. I am currently storing some mostly numeric values as ids in
> a db but with the possiblity that there are revisions to each. For
> instance say we have a record such as 100, a revision may need to be added
> such as 100a. As a result this column in the db must be set to a string
> data type. However, when I do so the sort order gets whacked. We have
> some records in the hundreds and they show up after those in the thousands
> as a result of string sorting. For example, record 500 is less than 1000
> and so shows up below. However, we need it to sort as though it were
> sorting integers but with the added requirement of allowing for the
> revisions. Is this possible or should I come up with a different scheme
> as far as revisions go such as tack on the revision tag later in
> processing and not store it in the db (this is undesirable).
>
> If you made it this far, thanks for reading :),[/color]

I don't see this as a C# problem, but rather a DB problem.

I would go for a solution that makes it possible to "sort" the table
directly from the database (with an "ORDER BY" clause).

This means that my solution would be something like this:

CREATE TABLE humhum
(
id numeric,
revision char(1) DEFAULT '0',
...,

CONSTRAINT humhum_pk PRIMARY KEY (id, revision)
)

....then it's simple to make a select ... order by id, revision.

// Bjorn A

[MuZZy]

Re: Sorting String Values

Bjorn Abelli wrote:[color=blue]
> "John Smith" wrote...
>
>[color=green]
>>New problem. I am currently storing some mostly numeric values as ids in
>>a db but with the possiblity that there are revisions to each. For
>>instance say we have a record such as 100, a revision may need to be added
>>such as 100a. As a result this column in the db must be set to a string
>>data type. However, when I do so the sort order gets whacked. We have
>>some records in the hundreds and they show up after those in the thousands
>>as a result of string sorting. For example, record 500 is less than 1000
>>and so shows up below. However, we need it to sort as though it were
>>sorting integers but with the added requirement of allowing for the
>>revisions. Is this possible or should I come up with a different scheme
>>as far as revisions go such as tack on the revision tag later in
>>processing and not store it in the db (this is undesirable).
>>
>>If you made it this far, thanks for reading :),[/color]
>
>
> I don't see this as a C# problem, but rather a DB problem.
>
> I would go for a solution that makes it possible to "sort" the table
> directly from the database (with an "ORDER BY" clause).
>
> This means that my solution would be something like this:
>
> CREATE TABLE humhum
> (
> id numeric,
> revision char(1) DEFAULT '0',
> ...,
>
> CONSTRAINT humhum_pk PRIMARY KEY (id, revision)
> )
>
> ...then it's simple to make a select ... order by id, revision.
>
> // Bjorn A
>[/color]

Or if you can't change the db stucture for any reason, sort by int part of the column values, which
may be slow though. I would vote for Bjorn's solution