Delphi Table sorting

Question

I have a simple problem which gave em a headache

I need to sort integers in a Database table TDBGrid ( its ABS database from component ace ) with the following order

0
1
11
111
121
2
21
211
22
221

and so on

which means every number starting with 1 should be under 1

1
11
111
5
55

can anyone help me?

thanks

If they are integers how to make a difference from 01 and 1? May be you meant strings? And if they are strings, the normal sorting should work. — giacoder
– giacoder, Commented Nov 27, 2011 at 0:32
the normal sorting doesnt work for simple reason 22 is smaller than 0111 — BlitzCrank
– BlitzCrank, Commented Nov 27, 2011 at 0:34
I would like to use string but i have some condition set that can be only applied on integer — BlitzCrank
– BlitzCrank, Commented Nov 27, 2011 at 0:35
I don't quite understand how you make a difference between 0111 and 111 if they are integers? — giacoder
– giacoder, Commented Nov 27, 2011 at 0:39

Wouter van Nifterick · Accepted Answer · 2011-11-27 17:37:41Z

1

This should work to get stuff in the right order:

Convert the original number to a string;
Right-pad with zeroes until you have a string of 3 characters wide;
(optional) Convert back to integer.

Then sorting should always work the way you want. Probably it's best to let the database do that for you. In MySQL you'd do something like this:

select RPAD(orderid,3,'0') as TheOrder 
from MyTable 
order by 1

answered Nov 27, 2011 at 17:37

Wouter van Nifterick

24.2k7 gold badges82 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ken White · Accepted Answer · 2011-11-27 01:45:01Z

1

I just ran this in SQL Server Management Studio - note I mixed up the rows in the input so they were not in sorted order:

create table #temp( ID Char(3));
insert into #temp (ID) 
      select '111' union
      select '221';
      select '0' union 
      select '21' union
      select '1' union 
      select '11' union
      select '211' union
      select '121' union
      select '2' union
      select '22' union
select * from #temp order by ID;

I got the following output:

ID
----
0  
1  
11 
111
121
2  
21 
211
22 
221

(10 row(s) affected)

If you're getting different results, you're doing something wrong. However, it's hard to say what because you didn't post anything about how you're retrieving the data from the database.

Edit: Some clarification by the poster indicates that the display is in a TDBGrid attached to a table using Component Ace ABS Database. If that indeed is the case, then the answer is to create an index on the indicated column, and then set the table's IndexName property to use that index.

edited Nov 27, 2011 at 1:45

answered Nov 27, 2011 at 1:10

Ken White

126k15 gold badges237 silver badges476 bronze badges

12 Comments

BlitzCrank Over a year ago

its a component, it sorts them by default, TDBGrid and as i said the database is ABS from component ace, its a file driven database.

Ken White Over a year ago

TDBGrid does NOT sort anything by default. You have to do that either by putting an index on the table and then using that index order, or by adding an ORDER BY in your SQL.

BlitzCrank Over a year ago

there is an index so how do i do achieve the sorting i need

Ken White Over a year ago

@Sertac, the poster is using a TDBGrid attached to a table from Component Ace ABS. I feel like we're trying to pull teeth here trying to get information from the poster - 15 comments to the original question, and it wasn't until I posted an answer that we found out it was a TDBGrid, and now there are 5 comments to my answer (six with this one).

Sertac Akyuz Over a year ago

@user - My suggestion would be to start a new project with few lines of code and a table from scratch that would duplicate the problem and contact ace support. As you have found out from this answer and the comments to the question, string sorting should give the result that you want.

|

Branko · Accepted Answer · 2011-11-27 18:32:29Z

0

select cast(INT_FIELD as varchar(9)) as I from TABxxx order by 1

answered Nov 27, 2011 at 18:32

Branko

1,5681 gold badge18 silver badges36 bronze badges

Collectives™ on Stack Overflow

Delphi Table sorting

3 Answers 3

Comments

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related