Re: [sqlite] Function to use to convert a text float to a float for use in ORDER BY

[Tim Streater]

On 17 May 2018, at 23:13, Keith Medcalf  wrote:

> select col1, col2 from mytable order by cast(body_size as float);

Thank you. That's much better (although abs(body_size) worked too).



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

Re: [sqlite] Function to use to convert a text float to a float for use in ORDER BY

[Abroży Nieprzełoży]

> select col1, col2 from mytable order by cast(body_size as float);

And if you care about speed of select, you could create appropriate index:

create index mytable_bodysize_index on mytable(cast(body_size as float));


2018-05-18 0:13 GMT+02:00, Keith Medcalf :
>
> Why not encode (speak / say) what you want to do directly, rather than
> prayerfully relying on implementation details --
>
> select col1, col2 from mytable order by cast(body_size as float);
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
>
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of Tim Streater
>>Sent: Thursday, 17 May, 2018 15:42
>>To: SQLite mailing list
>>Subject: [sqlite] Function to use to convert a text float to a float
>>for use in ORDER BY
>>
>>My db has a table with a column defined thus:
>>
>>  body_size text default '0.0'
>>
>>whose purpose is to hold the size of the item that the row
>>represents. All rows contain a value but as a string to one decimal
>>place, not a number. So the column contains strings such as '0.0',
>>'3.7', '22.9', etc. All are positive.
>>
>>Now I want to use the body_size in an ORDER BY so I'll need SQLite to
>>convert the values to a float. What will be the best function to use?
>>I'd thought of this:
>>
>>  select col1, col2 from mytable order by round(body_size,1)
>>
>>however the doc doesn't specify whether round accepts a non-numeric
>>argument. Perhaps:
>>
>>  select col1, col2 from mytable order by abs(body_size)
>>
>>would be better, but I wonder which is fastest (although speed may
>>not matter too much, the typical select shouldn't return more than
>>few thousand rows).
>>
>>
>>--
>>Cheers  --  Tim
>>___
>>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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Function to use to convert a text float to a float for use in ORDER BY

[Keith Medcalf]

Why not encode (speak / say) what you want to do directly, rather than 
prayerfully relying on implementation details --

select col1, col2 from mytable order by cast(body_size as float);


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Tim Streater
>Sent: Thursday, 17 May, 2018 15:42
>To: SQLite mailing list
>Subject: [sqlite] Function to use to convert a text float to a float
>for use in ORDER BY
>
>My db has a table with a column defined thus:
>
>  body_size text default '0.0'
>
>whose purpose is to hold the size of the item that the row
>represents. All rows contain a value but as a string to one decimal
>place, not a number. So the column contains strings such as '0.0',
>'3.7', '22.9', etc. All are positive.
>
>Now I want to use the body_size in an ORDER BY so I'll need SQLite to
>convert the values to a float. What will be the best function to use?
>I'd thought of this:
>
>  select col1, col2 from mytable order by round(body_size,1)
>
>however the doc doesn't specify whether round accepts a non-numeric
>argument. Perhaps:
>
>  select col1, col2 from mytable order by abs(body_size)
>
>would be better, but I wonder which is fastest (although speed may
>not matter too much, the typical select shouldn't return more than
>few thousand rows).
>
>
>--
>Cheers  --  Tim
>___
>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

[sqlite] Function to use to convert a text float to a float for use in ORDER BY

[Tim Streater]

My db has a table with a column defined thus:

  body_size text default '0.0'

whose purpose is to hold the size of the item that the row represents. All rows 
contain a value but as a string to one decimal place, not a number. So the 
column contains strings such as '0.0', '3.7', '22.9', etc. All are positive.

Now I want to use the body_size in an ORDER BY so I'll need SQLite to convert 
the values to a float. What will be the best function to use? I'd thought of 
this:

  select col1, col2 from mytable order by round(body_size,1)

however the doc doesn't specify whether round accepts a non-numeric argument. 
Perhaps:

  select col1, col2 from mytable order by abs(body_size)

would be better, but I wonder which is fastest (although speed may not matter 
too much, the typical select shouldn't return more than few thousand rows).


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