Bookstore

Recent

Author Topic: Dataset Natural Sort  (Read 466 times)

Fahmy Rofiq

  • Jr. Member
  • **
  • Posts: 71
Dataset Natural Sort
« on: March 16, 2020, 06:07:31 am »
How to do natural sorting on dataset?
thanks..
Lazarus + FPC Trunk
Windows 10 x64

mangakissa

  • Hero Member
  • *****
  • Posts: 980
Re: Dataset Natural Sort
« Reply #1 on: March 16, 2020, 10:10:01 am »
It depends what you mean.

Natural sorting is like 'select <fields> from <table>'. It's sequential sorting but no sorting at all.
Using  'select <fields> from <table> order by <field>' does sort your query but is not natural.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

rvk

  • Hero Member
  • *****
  • Posts: 3943
Re: Dataset Natural Sort
« Reply #2 on: March 16, 2020, 10:16:32 am »
https://en.m.wikipedia.org/wiki/Natural_sort_order

You need to specify the underlying database.
The sorting is done by the database.

For instance... Firebird has NUMERIC-SORT for its collation.
https://firebirdsql.org/refdocs/langrefupd25-ddl-collation.html

Fahmy Rofiq

  • Jr. Member
  • **
  • Posts: 71
Re: Dataset Natural Sort
« Reply #3 on: March 16, 2020, 11:05:42 am »
Yes i'm using firebird
Lazarus + FPC Trunk
Windows 10 x64

rvk

  • Hero Member
  • *****
  • Posts: 3943
Re: Dataset Natural Sort
« Reply #4 on: March 16, 2020, 11:14:42 am »
Yes i'm using firebird
Then you can create a new collation like this
Code: SQL  [Select]
  1. CREATE collation unicode_num FOR utf8
  2. FROM unicode 'NUMERIC-SORT=1';

After that you can use that collation to create your fields.
If you already have the table, you can apply the collation during a select/order by.
Code: SQL  [Select]
  1. SELECT * FROM TABLE ORDER BY your_field COLLATE unicode_num

(Declaring it in the table will be faster because you can create indexes on it)

egsuh

  • Sr. Member
  • ****
  • Posts: 317
Re: Dataset Natural Sort
« Reply #5 on: March 25, 2020, 11:02:41 pm »
Recently I read a book on relational database, which says that relations (i.e. tables of databases) are sets in principle, which means there are no intrinsic order. There are nothing like natural sort. This means that you should supply your own criteria to order / sort, as far as I understand.

Well this is rather philosophical approach and many databases provide orders through many ways, e.g. auto-created keys, but the book says that such keys are not the intrinsic part of relational database concepts.