CODEWITHSUNDEEP
delphisorting
BlitzCrank
BlitzCrank

Reputation: 413

Delphi Table sorting

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

and so on

which means every number starting with 1 should be under 1

can anyone help me?

thanks

Upvotes: 1

Views: 1189

Answers (3)

Branko
Branko

Reputation: 1458

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

Upvotes: 0

Wouter van Nifterick
Wouter van Nifterick

Reputation: 24086

This should work to get stuff in the right order:

  1. Convert the original number to a string;
  2. Right-pad with zeroes until you have a string of 3 characters wide;
  3. (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

Upvotes: 1

Ken White
Ken White

Reputation: 125687

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.

Upvotes: 1

Related Questions