Converting values within ORDER BY clause

Question

Considering the following query:

SELECT 1 AS a, '100' AS b
UNION
SELECT 1 AS a, '50' AS b
ORDER BY a, b;

which results in:

a    b
1  '100'
1  '50'

What I really want is to sort column b by number and not text.

One possible solution could be:

SELECT 1 AS a, '100' AS b, '100'::int AS c
UNION
SELECT 1 AS a, '50' AS b, '50'::int AS c
ORDER BY a, c;

Which results in the ordering of:

a    b     c
1  '50'   50
1  '100'  100

as desired.

This is quite satisfying but if I have 1 mio. result rows then I would also have 1 mio. values transferred in the result response which I do not need.

Is there a neater way of converting column values when ordering?

I am looking for a way of letting the SQL server convert the column values "within" the ORDER BY clause but only returning the "original" result columns.

you may want to look at this function too. http://stackoverflow.com/questions/16195986/isnumeric-with-postgresql — Paul Maxwell, Aug 27 '14 at 13:51

Raphaël Althaus · Accepted Answer · 2014-08-27T12:59:44.377

1

you can do this with a subquery (think the union is part of the problem).

select a, b
from (
      SELECT 1 AS a, '100' AS b
      UNION
      SELECT 1 AS a, '50' AS b)s
order by cast(b as int)-- or b::int

see SqlFiddle with difference

But if it's just a sample, and b is a varchar type in your table, you can do

select a, b
from YourTable
order by a, cast(b as int)

without any subquery.

edited Aug 27 '14 at 12:59

answered Aug 27 '14 at 12:48

Raphaël Althaus

59,727
6
96
122

@marue if it's coming from a table, and b is a varchar type, you can perfectly do `order by b::int` – Raphaël Althaus Aug 27 '14 at 12:58
I would suggest staying with a string and avoid casting to int. As usual too late. – Paul Maxwell Aug 27 '14 at 13:07
@Used_By_Already and how would you get the desired order, then ? – Raphaël Althaus Aug 27 '14 at 13:13

Paul Maxwell · Answer 2 · 2014-08-27T13:24:57.340

Dealing with strings is best handled by strings, if any value in that column isnt convertible to integer it will fail.

SELECT
      a, b
FROM (
      SELECT 1 AS a, '100' AS b
      UNION ALL
      SELECT 1 AS a, '50' AS b
      UNION ALL
      SELECT 1 AS a, 'badvalue' AS b
     ) s
ORDER BY
      a, right(concat('000000000',b),8)
;

| A |        B |
|---|----------|
| 1 |       50 |
| 1 |      100 |
| 1 | badvalue |

BUT:

SELECT
      a, b
FROM (
      SELECT 1 AS a, '100' AS b
      UNION ALL
      SELECT 1 AS a, '50' AS b
      UNION ALL
      SELECT 1 AS a, 'badvalue' AS b
     ) s
ORDER BY
      a, b::int
;

ERROR: invalid input syntax for integer: "badvalue": SELECT

http://sqlfiddle.com/#!15/d41d8/3109

EDIT in response to leading +/- negative characters

SELECT
      a, b
FROM (
      SELECT 1 AS a, '-100' AS b
      UNION ALL
      SELECT 1 AS a, '-50' AS b
      UNION ALL
      SELECT 1 AS a, '100' AS b
      UNION ALL
      SELECT 1 AS a, '50' AS b
      UNION ALL
      SELECT 1 AS a, 'badvalue' AS b
     ) s
ORDER BY
      a
    , case when left(b,1) = '-' then right(concat('000000000',b),8)
           else right(concat('11111111',replace(b,'+','')),8)
      end
;

http://sqlfiddle.com/#!15/d41d8/3112

just to mention: this would not work with strings containing + or - at the first character, but despite that it would also be a possible solution for me — m13r, Aug 27 '14 at 13:15

score 0 · Answer 3 · answered Aug 27 '14 at 12:43

0

Yes either just use numbers in the insert

SELECT 1 AS a, 100 AS b
UNION
SELECT 1 AS a, 50 AS b
ORDER BY a, b;

cast to a number before insert,

SELECT 1 AS a, cast('100' as integer) AS b
UNION
SELECT 1 AS a, cast('50' as integer) AS b
ORDER BY a, b;

or in Order By

SELECT 1 AS a, 100 AS b
UNION
SELECT 1 AS a, 50 AS b
ORDER BY a, cast(b as integer);

answered Aug 27 '14 at 12:43

Charles Bretana

143,358
22
150
216

I want b to stay in the original data type (in my case: text) – m13r Aug 27 '14 at 12:45

score 0 · Answer 4 · edited Jun 20 '20 at 09:12

0

Cast the column b values.

Example:

order by a, cast(b as numeric)

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 27 '14 at 12:47

Adi

232
1
9

Converting values within ORDER BY clause

4 Answers4

Example: