"printf" with "as" and "order by"

(1.2) By cj (sqlitening) on 2022-01-30 07:16:01 edited from 1.1 [link] [source]

Create table if not exists T1 (
  CNUM  integer primary key,
  PRICE integer)

Insert into T1 values(1,1995),(2,1300),(3,4995),(4,101),(5,703)

select CNUM, printf('%.2f',PRICE *.01) AS PRICE from T1 order by PRICE
CNUM   PRICE
4      1.01
2      13.00
1      19.95
3      49.95
5      7.03
-----------------------------------------------------------------------------
These work as expected:
select CNUM, printf('%.2f',PRICE *.01) AS PRICE  from T1 order by PRICE * .01
select CNUM, printf('%.2f',PRICE *.01) AS AMOUNT from T1 order by PRICE
select CNUM, printf('%.2f',PRICE *.01) from T1 order by PRICE
CNUM   PRICE (or AMOUNT)
4      1.01
5      7.03
2     13.00  
1     19.95   
3     49.95 

What is the correct way to write this so results are ordered numerically
and the column names are the same as in table?

(2.1) By Larry Brasfield (larrybr) on 2022-01-29 23:46:45 edited from 2.0 in reply to 1.0 [link] [source]

select CNUM, printf('%.2f',PRICE *.01) AS PRICE from T1 order by cast(PRICE as real);

The difficulty you have arises from the affinity of expressions and the fact that the printf() function yields a text value. Even though it is text that looks like numbers, you need to convert it to a number to get it to sort numerically.

(Amend via edit:)

Regarding getting result columns named as you want: using <expression> AS <name_you_like> for each column in the results is the standard and reliable way to do that.

(3) By Keith Medcalf (kmedcalf) on 2022-01-29 23:52:29 in reply to 1.0 [source]

How about:

select cnum,
       printf('%.02f', price / 100.) as price
  from
       (
          select cnum,
                 price
            from t1
        order by price
       )
;

That is, order the results before converting to text string. text strings sort in binary (alphanumeric) order. If you wish to sort by values, then you must sort the value's and not the "text representation".

This will also be far more efficient if you happen to have the correct indexes defined (on T1.price) -- that index cannot be used at all in Larry's query.

(4) By cj (sqlitening) on 2022-01-30 01:44:24 in reply to 3 [link] [source]

Thank you for the fast reresponse and great explanation!

(5) By Keith Medcalf (kmedcalf) on 2022-01-30 07:49:12 in reply to 3 [link] [source]

The following will also work:

  select CNUM, 
         printf('%.2f',PRICE *.01) AS PRICE 
    from T1 
order by t1.price
;

because it requires ordering by the table column t1.price, not the output column price.

(6) By cj (sqlitening) on 2022-01-30 08:59:21 in reply to 5 [link] [source]

Keith,

Your solution is great.
Added index to price and tried using ABS which is incorrect.
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG
 LOCAL s AS STRING
 slOpen  "junk.db3","C"
 slexe   "create table if not exists t1(cnum integer primary key, price integer)"
 slexe   "create index if not exists price_idx on t1(price)"
 slexe   "insert into t1 values(null,1995),(null,1300),(null,4995),(null,101),(null,703)"
 s = rs( "select cnum,printf('%.2f',PRICE * .01) AS price from T1 order by t1.price") 'correct
 s+= rs( "select cnum,abs(printf('%.2f',PRICE * .01)) AS price from T1 order by price") 'wrong
 ? s
END FUNCTION

cnum  price
 4      1.01
 5      7.03
 2      13.00  (keith correct)
 1      19.95
 3      49.95

 cnum   price
 4      1.01
 5      7.03
 2      13.0   (abs wrong)
 1      19.95
 3      49.95