???Logic behing BY - Progress Sorting???

[saravanakumar]

Hi Everybody,
I am stuck up with the below issue, can anyone please help me out in this regard. I have provided a code snippet which is easily understandable (...do agree not an optimized one. Sorry), i need to know how the sorting works in Progress 4GL. [Client has raised the same query. V need a ping point answer... Plzzz Friendsssss]

I need to know the following things;
1) How does the BY work (logic behind the BY).
2) Is it a Character by Character sorting ?(...if so the result set that i get is wrong for the below code)
3) Is it an ASCII character set sorting or any other?

DEFINE TEMP-TABLE ttname
FIELD ttname1 AS CHAR.

CREATE ttname.
ASSIGN ttname1 = "Pragadeesh".

CREATE ttname.
ASSIGN ttname1 = "1Pragadeesh".

CREATE ttname.
ASSIGN ttname1 = "2Pragadeesh".

CREATE ttname.
ASSIGN ttname1 = "12345".

CREATE ttname.
ASSIGN ttname1 = "Pragadeesh3".

CREATE ttname.
ASSIGN ttname1 = "11111".

CREATE ttname.
ASSIGN ttname1 = "pragadeesh".

CREATE ttname.
ASSIGN ttname1 = "1PRAGAdeesh".

CREATE ttname.
ASSIGN ttname1 = "1pragadeesh".

CREATE ttname.
ASSIGN ttname1 = "1pragadeesh".

CREATE ttname.
ASSIGN ttname1 = "Pragadeesh3".

CREATE ttname.
ASSIGN ttname1 = "PRAGAdeesh3".

CREATE ttname.
ASSIGN ttname1 = "Praga deesh3".

CREATE ttname.
ASSIGN ttname1 = "2pragaDEESH".

CREATE ttname.
ASSIGN ttname1 = " pragadeesh".

FOR EACH ttname NO-LOCK BY ttname1:
DISP ttname.ttname1 FORMAT "X(15)".
END.

Result Set: (ASCENDING)
pragadeesh
11111
12345
1Pragadeesh
1pragadeesh
1pragadeesh
1PRAGAdeesh
2Pragadeesh
2pragaDEESH
Praga deesh3
pragadeesh
Pragadeesh
Pragadeesh3
Pragadeesh3
PRAGAdeesh3

Result Set 1: (DESCENDING)
PRAGAdeesh3
Pragadeesh3
Pragadeesh3
Pragadeesh
pragadeesh
Praga deesh3
2pragaDEESH
2Pragadeesh
1pragadeesh
1pragadeesh
1PRAGAdeesh
1Pragadeesh
12345
11111
pragadeesh

Here at least the ascending order should be the exact reverse of the descending order but it is not so. WHY???

Thanks in advance. :dizzy:

 

[MrMoo]

1) By is the equivalent of Order By I believe
2 & 3) I would assume from your result sets that Progress does a case insensitive sort. space then numbers then characters

 

[saravanakumar]

1) Agreed.
2) Agreed. (Doubtful)

Bcoz if you see the below result set; if they are case insensitive sort then the few results that are bolded should be in the same order as it is created. But i don't think so it is the same. Can you plz let me know the reason?

Result Set: (ASCENDING)
pragadeesh
11111
12345
1Pragadeesh
1pragadeesh
1pragadeesh
1PRAGAdeesh
2Pragadeesh
2pragaDEESH
Praga deesh3
pragadeesh
Pragadeesh
Pragadeesh3
Pragadeesh3
PRAGAdeesh3

If anyone else disagrees to this, plz provide more stuff on the same? Plzzz do the needful.

Plzzz... Thanks in advance.

 

[tamhas]

If they are all equivalent as compared in the sorting, why would you think their sequence within the group was predictable. If you want the sort to be predictable, add a second field for when the first is tied.

 

[Balwinder]

Try adding an index to the temp table. It might make a difference.

 

[taqvia]

Very good topic . it helped me to revisit different sort algorithms learnt during college days.after looking into it and help from friend at least able to resolve one of ur concern that the ascending and descneding shld be in same sequence.
Its not only progress u will find the same sorting result in excel.

For this just modify as below:
/* Progress by default is not case sensitive - Arshad */
DEFINE TEMP-TABLE ttname
FIELD ttname1 AS CHAR CASE-SENSITIVE.


Arshad

 

[saravanakumar]

Hi Taqvia,
Thanks for ur valuable information. I tried the same with CASE-SENSITIVE, it worked fine. But i am bit concerned with the order that i have highlighted in my previous post.

As you mentioned, I got the same result in Excel as well. I do wonder the sorting logic behind this. If you get more information please add up to this thread.

Originally Posted by Tamhas:
If they are all equivalent as compared in the sorting, why would you think their sequence within the group was predictable. If you want the sort to be predictable, add a second field for when the first is tied.

Hi Tamhas - These string might be equal to Progress 4GL, but i am concerned with the business problem. It is not same when you come back to the real time scenario based on ASCII values. I do agree that we need a secondary sort as well to resolve this problem if there is no other go. But it seems to be a overhead to me.

Thanks in advance.

 

[tamhas]

ABL has all the mechanisms you can want ... you just have to decide what the business rules are. Case-sensitive or not. There are mechanisms for sorting international character sets appropriately. If two or more entries tie according to the current rule, there has to be some reason why you expect them in a particular order. Whatever that is, include in in the sort specification. Define the rule and you can implement it.

 

[saravanakumar]

Hi Tamhas,
Agreed. Thanks for ur valuable comments. Correct me if i am wrong at any instance.

Originally Posted by Tamhas:
you just have to decide what the business rules are. Case-sensitive or not.

Fine. This can be solved using the keyword "CASE-SENSITIVE" as mentioned in previous post. Totally Agreed.

Originally Posted by Tamhas:
There are mechanisms for sorting international character sets appropriately. If two or more entries tie according to the current rule, there has to be some reason why you expect them in a particular order. Whatever that is, include in in the sort specification. Define the rule and you can implement it.

1) some reason - ASCII Character SET SORT is required.

2) include in in the sort specification - HOW??? Is there any specific way, If so can you please show me the right path for the same with a small code snippet?

3) Define the rule and you can implement it - Do you mean to say that we need to write our own sorting logic? If so its fine for me (...can be implemented), want to know if there is a way to provide the rule directly in Progress 4GL.

Please do the needful. Thanks in advance.

 

[tamhas]

No, no. Sort sequence is a property of the character set, whatever your DB is encoded to. There are rules which define the sort order for both case insensitive and case sensitive sorts. They can be overridden, but I doubt that is your problem.

By business rule I mean that you have to have some reason that tells you that one record with a key of ABC is supposed to sort before or after one that also has a key of ABC ... date of entry, invoice total, age, whatever .... whatever that is, include it in the sort specification as a tiebreaker.

If this isn't clear, perhaps you need to restate your problem. You've said various things in this thread and seen to have come to some resolutions, but it is no longer clear to me what exactly your problem is. Be clear and provide a specific example.

 

[saravanakumar]

Fine. Totally agreed to your points. I have now solved the issue using a secondary field if any tie-breaker occurs.

Thanks a lot for your valuable information.

 

[tamhas]

Once one sees the problem clearly, the solution is often not difficult!

 

[saravanakumar]

Re: ???Logic behind BY - Progress Sorting???

Originally posted by tamhas:
Once one sees the problem clearly, the solution is often not difficult!

Ya i do agree. But to add up a point on that bringing an optimal solution is more important than bringing out a solution to it.

If a sort can be done using a single row and still we are going for a secondary sort it doesn't sound good. I want to know if we could bring out an appropriate solution initially and then after reading the posts i got to know that we need a secondary field in my case. One point i like to high light is i can't put the coding stuff as such (...confidential from client's perspective). So i have re-modified my example and have put my question in more generic format. In my initial post itself i have mentioned if i have not made it clear i am sorry. Sorry if i have made a mesh up here.

I have got the answer that i required. Thanks for one and all. Thanks for your quick response.

 

[TomBascom]

Optimization has many dimensions. You might, for instance, be optimizing:

1) Runtime
2) IO (logical or physical)
3) RAM utilization
4) Disk space utilization
5) Readability
6) Provability
7) Maintainability
8) Portability

It is often the case that some of these conflict with one another.

You seem to be going for brevity of expression which is probably contrary to most of the above.

 

[saravanakumar]

Hi TamBoscom,
Agreed. Finally the thing is i have got the overall idea over this concept which i expected.

So thanks for all who have replied quickly and informatively.

Thanks guys...