Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 5 of 5 total
Thread Index Newbie
Mon, Feb 9 2009 5:38 AMPermanent Link

"Petter Topp"
Hi I have a table with a string field.

The Data look like this:

1
1 +
1 -
2
2 +
2 -
3
3 +
3 -

I would like the data sorted like this instead:

1 -
1
1 +
2 -
2
2 +
3 -
3
3 +

Is there any way to accomplish this?
(I would rather not use an extra field to handle this)

Regards
Petter Topp
Mon, Feb 9 2009 11:31 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Petter


I can't think of a way in DBISAM. Even if you split the sign off you'd still have to process the data somehow or define your own collation sequence. If you look at the suffixes you have an empty space char(0), + char(43) and - char(45). Those have to be changed so that they sort in the order you want. You might be able to do something with custom functions but I doubt it since ORDER BY or an index requires a physical field.

I think your best bet is going to be an extra field.

Roy Lambert [Team Elevate]
Tue, Feb 10 2009 3:11 AMPermanent Link

"Petter Topp"
Thanks Robert.

I figured that an extra field would solve this, but I will be a headache to
maintain
Will just have to live with it the way it is...

Regards
Petter

"Roy Lambert" <roy.lambert@skynet.co.uk> skrev i melding
news:B580FC4B-7084-430B-A6D4-AE7D56E9D556@news.elevatesoft.com...
> Petter
>
>
> I can't think of a way in DBISAM. Even if you split the sign off you'd
> still have to process the data somehow or define your own collation
> sequence. If you look at the suffixes you have an empty space char(0), +
> char(43) and - char(45). Those have to be changed so that they sort in the
> order you want. You might be able to do something with custom functions
> but I doubt it since ORDER BY or an index requires a physical field.
>
> I think your best bet is going to be an extra field.
>
> Roy Lambert [Team Elevate]
>
Tue, Feb 10 2009 4:28 AMPermanent Link

"John Hay"
Petter
> The Data look like this:
> 1
> 1 +
> 1 -
> 2
> 2 +
> 2 -
>
> I would like the data sorted like this instead:
> 1 -
> 1
> 1 +
> 2 -
> 2
> 2 +

If you are using a query to retrieve the data what about

SELECT DataField,SUBSTRING(DataField FROM 1 FOR IF(POSITION(' ',DataField) >
0 THEN POSITION(' ',DataField)-1 ELSE 20)) AS t2,
IF(POSITION('-',DataField) > 0 THEN -1 ELSE
IF(POSITION('+',DataField) > 0 THEN 1 ELSE 0)) AS t3
FROM DataTable
ORDER BY t2,t3

John

Tue, Feb 10 2009 5:45 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

John


Well done - that was one of my thoughts but I couldn't get the ORDER BY to work.

Roy Lambert
Image