Topic: Sorting problem when creating a report

Hi,

I am using version 1.42 and I have an issue when sorting SQL Queries result as shown on attached picture. Sorting is not accurate with numbers containing dots.

4.4.1 should be followed by 4.4.2, then 4.4.3 but in the software, sorting is done like this: 4.4.1, 4.4.10, 4.4.11, 4.4.2 which is not helping with the kind of report I am looking for.


Is it possible to gather multiple reports in one report? 

For example, I want to report 2 or more SQL queries in the same report to get a broad overview.

Post's attachments

Attachment icon report-sorting.jpg 47.1 kb, 446 downloads since 2014-08-26 

Re: Sorting problem when creating a report

Hello,


I can offer only a bit strange solution, example:

SELECT
fieldnumber
FROM tablename
ORDER BY
CAST (trim(substr(replace(fieldnumber, '.', '     '), 1, 3)) AS INTEGER),
CAST (trim(substr(replace(fieldnumber, '.', '     '), 7, 5)) AS INTEGER),
CAST (trim(substr(replace(fieldnumber, '.', '     '), 12)) AS INTEGER);

It's work with values from x.x.x to xxx.xxx.xxx

Dmitry.

Re: Sorting problem when creating a report

This problem is very common. This is of course because the 4.4.10 field is a text field. I usually add another field which is number type, and fill in the numbers manually how I want to sort the 4.4.10 field. You can than sort on the number field instead on the text field. After that you can hide the number field, by using it in the query and not using it in the report.

Re: Sorting problem when creating a report

@DriveSoft,

Many many thanks, it is working like a charm, you saved my day!!


@Flying2birds,

Yes, it is a very common problem, I have read a lot of articles on the Internet about it and I never found any simple relevant solution adapted to Sqlite. There are verycomplex queries adapted to Oracle & SqlServer which, in my opinion are, by far too complex compared to this simple trick!

I have even tried with Integer columns with no luck and I am not eager at all to fill in more than 250 rows manually which is why I use a database smile

Simplicity is my best friend wink

Re: Sorting problem when creating a report

I think you share a great post keep it up these type of. I have faced many times of sorting problem when creating a report. THanks a lot

feathers