Re: [sqlite] Ordering looks incorrect

[David Crayford]

On 14/09/2016 9:59 PM, R Smith wrote:
I think this is answered, but since you are a bit new to SQLite, and 
to be somewhat more informant...


SQLite is certainly a different experience to the enterprise class data 
bases that I'm used to but I like it. I'm still getting used to the duck 
typing aspect.
I've built it on a z/OS mainframe with very little changes and it works 
great in the z/OS UNIX environment. I'm wondering what it will take to 
get it to work

in the native file system.



On 2016/09/14 3:19 PM, David Crayford wrote:
Of course. How do I do something similar to DB2 decimal() function 
which will add trailing zeros to precision? I tried round(col,6) but 
it knocked off the trailing zero like 12.12345.


It's not so much the trailing zeroes you need, it's the leading 
spaces. But you should order by the original value, not by "column 2" 
because "column 2" contains RESULTS (or OUTPUT if you will), not 
original values, and you have stringified the results with your 
printf() statement, so they will now sort like strings.




Got it, the sort on original value was what I needed. And thank you to 
you chaps for pointing it out.


At least, this is how SQLite thinks of that statement - I'm unsure if 
this is in line (or not in line) with any standard. I think from a 
previous discussion we concluded that ordering, limiting and 
offsetting were all non-standard adaptions by various SQL 
implementations and so have no true conformance spec.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[R Smith]

I think this is answered, but since you are a bit new to SQLite, and to 
be somewhat more informant...


On 2016/09/14 3:19 PM, David Crayford wrote:
Of course. How do I do something similar to DB2 decimal() function 
which will add trailing zeros to precision? I tried round(col,6) but 
it knocked off the trailing zero like 12.12345.


It's not so much the trailing zeroes you need, it's the leading spaces. 
But you should order by the original value, not by "column 2" because 
"column 2" contains RESULTS (or OUTPUT if you will), not original 
values, and you have stringified the results with your printf() 
statement, so they will now sort like strings.


At least, this is how SQLite thinks of that statement - I'm unsure if 
this is in line (or not in line) with any standard. I think from a 
previous discussion we concluded that ordering, limiting and offsetting 
were all non-standard adaptions by various SQL implementations and so 
have no true conformance spec.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[David Crayford]

On 14/09/2016 9:23 PM, Clemens Ladisch wrote:

David Crayford wrote:

How do I do something similar to DB2 decimal() function which will add trailing 
zeros to precision?

You could use the standard SQL CAST() to convert the value back into a number,
by why not simply sort by the original number to begin with?

   ORDER BY max(cpu1)


+1 that's the answer. Thank you so much. As you can tell I come from a 
very different background WRT database technology ;)




Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[Clemens Ladisch]

David Crayford wrote:
> How do I do something similar to DB2 decimal() function which will add 
> trailing zeros to precision?

You could use the standard SQL CAST() to convert the value back into a number,
by why not simply sort by the original number to begin with?

  ORDER BY max(cpu1)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[David Crayford]

Of course. How do I do something similar to DB2 decimal() function which 
will add trailing zeros to precision? I tried round(col,6) but it 
knocked off the trailing zero like 12.12345.



On 14/09/2016 9:10 PM, Clemens Ladisch wrote:

David Crayford wrote:

9.733366
595.509361
29.117646
28.607606
14.684294

The sort order of these strings looks correct.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[Dominique Devienne]

On Wed, Sep 14, 2016 at 3:10 PM, Clemens Ladisch  wrote:

> The sort order of these strings looks correct.
>

As hinted by Clemens, you're sorting text values, not reals. See below. --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.10.2 2016-01-20 15:27:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table stats (plan, cpu);
sqlite> insert into stats values ('pts46', 595.509361), ('pts46',
95.059669), ('DSNUTIL', 9.733366);
sqlite> .headers on
sqlite> .mode column
sqlite> select * from stats;
plancpu
--  --
pts46   595.509361
pts46   95.059669
DSNUTIL 9.733366
sqlite> select plan, max(cpu) from stats group by plan order by 2 desc;
planmax(cpu)
--  --
pts46   595.509361
DSNUTIL 9.733366
sqlite> select plan, printf("%.6f", max(cpu)) from stats group by plan
order by 2 desc;
planprintf("%.6f", max(cpu))
--  
DSNUTIL 9.733366
pts46   595.509361
sqlite>
sqlite> select typeof(max(cpu)), typeof(printf("%.6f", max(cpu))) from
stats group by plan;
typeof(max(cpu))  typeof(printf("%.6f", max(cpu)))
  
real  text
real  text
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[Simon Slavin]

On 14 Sep 2016, at 2:10pm, Clemens Ladisch  wrote:

> The sort order of these strings looks correct.

Heh.  Heh heh.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ordering looks incorrect

[Clemens Ladisch]

David Crayford wrote:
> 9.733366
> 595.509361
> 29.117646
> 28.607606
> 14.684294

The sort order of these strings looks correct.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users