ORDER BY Values especified in clausule "IN()" BUT in Generics SQL and Informix

Question

I expect no repeat another question... I can't find the solution. All the solution are for MySql.

I want order the result of my query by the values in the clausule IN. But in internet and stackoverflow just find the solution for "MySql" like here:

Order by FIELD in MYSQL

Ordering by specific field value first

MySQL ORDER BY FIELD with %

Using clausule FIELD or FIELD_IN_SET. Can I use something similar to this.

thanks!!

I just try... and it doesn't work. This is my query.

select * from (
    select 4 as dato1 from systables where tabid = 1 union
    select 2 as dato1 from systables where tabid = 1 union
    select 1 as dato1  from systables where tabid = 1 union
    select 3 as dato1  from systables where tabid = 1 
)
where dato1 in (4,2,1,3)
order by instr('4,2,1,3', dato1)

This is that the query show:

dato1
1
2
4
3

I don't undertand...

Gordon Linoff · Answer 1 · 2015-09-21T17:56:21.203

3

One approach that works in many databases is something like this:

where x in ('a', 'b', 'c')
order by instr('a,b,c', x)

Of course, delimiters can cause a problem, so this is safer:

where x in ('a', 'b', 'c')
order by instr(',a,b,c,', ',' || x || ',', )

edited Sep 21 '15 at 17:56

answered Sep 21 '15 at 15:33

Gordon Linoff

1,242,037
58
646
786

What does this instr return ? – underscore Sep 21 '15 at 15:36
Hypothetically, it returns a number representing the start offset in the string of the code you're looking for. So, when `x` is `b`, it looks for `',b,'` in `',a,b,c,'` and returns 3. This is distinct from the values that would be returned for `',a,'` or `',c,'`, and in correct order, so it works. Assuming you have an `INSTR()` function. IDS 12.10 does have [INSTR()](http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_2336.htm). – Jonathan Leffler Sep 22 '15 at 03:57
I just try... and it doesn't work. up, it's the query. Thanks! – Marquake Sep 22 '15 at 07:38
@Marquake . . . IBM does document `instr()` (https://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0258.htm%23ids_sqs_0258). Perhaps `CHARINDEX()` might work. – Gordon Linoff Sep 24 '15 at 21:51

Luís Marques · Accepted Answer · 2015-09-25T10:28:31.153

2

For informix 12.10 (Developer Edition) the INSTR() does not seem to be properly converting the function arguments to character types.

I did a explicit cast to VARCHAR and the INSTR() function starts to return proper values.

select
    dato1
from (
    select 4 as dato1 from systables where tabid = 1 union
    select 2 as dato1 from systables where tabid = 1 union
    select 1 as dato1  from systables where tabid = 1 union
    select 3 as dato1  from systables where tabid = 1
)
where dato1 in (4,2,1,3)
order by instr('4,2,1,3', CAST(dato1 AS VARCHAR(255)))

Returns:

EDIT:

To clarify the use of the INSTR() function:

select
    dato1
  , instr('4213', CAST(dato1 AS VARCHAR(255))) AS position
from (
    select 4 as dato1 from systables where tabid = 1 union
    select 2 as dato1 from systables where tabid = 1 union
    select 1 as dato1 from systables where tabid = 1 union
    select 3 as dato1 from systables where tabid = 1
)
where dato1 in (4,2,1,3)
order by instr('4213', CAST(dato1 AS VARCHAR(255)))

Returns:

dato1    position
    4           1
    2           2
    1           3
    3           4

That being said, the DECODE() suggestion from Ricardo seems to be a better option.

edited Sep 25 '15 at 10:28

answered Sep 23 '15 at 08:30

Luís Marques

1,381
1
7
10

Seems like it's casting into a `CHAR` with +1 length and filling with spaces, hence the need to a proper cast. The `INSTR` function was introduced on 11.70. Before that and for this given example you can do an ORDER BY DECODE(dato1, 4, 1, 2, 2, 1, 3, 3, 4, 5) – Ricardo Henriques Sep 24 '15 at 13:00
Hi, That's right Luís. Great! But I have a problem. I don't understand how works this function in this case. I'm thinking... If you have the query, after solves SELECT and WHERE, and before apply "order by" this will be: 1,2,3,4 and now we apply the "order by". I imagine this situation: `SELECT 1,2,3,4 ORDER BY instr('4,2,1,3', CAST(dato1 AS VARCHAR(255))` If I use pen and paper, I write this: `SELECT 1,2,3,4 ORDER BY instr('4,2,1,3', '1,3,5,7')` I don't understand. I'll be grateful if someone explain that to me. Thanks in advance! @luís-marques – Marquake Sep 25 '15 at 08:45
1

@ricardoHenriques I like the use of the DECODE function. – Luís Marques Sep 25 '15 at 10:01
@luís-marques for small cases, where the IN values are few it is simpler. – Ricardo Henriques Sep 25 '15 at 10:17

score 2 · Answer 3 · edited May 23 '17 at 11:51

This answer is only for the explanation of the solutions already presented by @luís-marques and @gordon-linoff.

Since version 11.70 informix has INSTR function that takes a string and looks for a substring, also given, and returns the character position in that string where an occurrence of that substring begins (IBM documentation says it is the end but it is a doc bug).

The solution you are using is:

SELECT
    dato1
FROM (
    SELECT 4 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 2 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 1 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 3 AS dato1 FROM systables WHERE tabid = 1
)
WHERE dato1 IN (4,2,1,3)
ORDER BY INSTR('4,2,1,3', CAST(dato1 AS VARCHAR(255)));

To get the notion of what are happening you can:

SELECT
    dato1,
    INSTR('4,2,1,3', CAST(dato1 AS VARCHAR(255))) AS instr_res
FROM (
    SELECT 4 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 2 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 1 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 3 AS dato1 FROM systables WHERE tabid = 1
)
WHERE dato1 IN (4,2,1,3)
ORDER BY 2;

That will output:

  dato1   instr_res

      4           1
      2           3
      1           5
      3           7

But bear in mind that you can have problems using the delimeters, has marked by @gordon-linoff.

For example:

    SELECT
            dato1,
            INSTR('444,44,4', CAST(dato1 AS VARCHAR(255))) AS instr_res
    FROM (
            SELECT   4 AS dato1 FROM systables WHERE tabid = 1 UNION
            SELECT  44 AS dato1 FROM systables WHERE tabid = 1 UNION
            SELECT 444 AS dato1 FROM systables WHERE tabid = 1
    )
    WHERE dato1 IN (444,44,4)
    ORDER BY 2;

  dato1   instr_res

      4           1
     44           1
    444           1

To get this sorted always delimited the start and end of the values you want, for this case it will be:

    SELECT
            dato1,
            INSTR(',444,44,4,', ','||CAST(dato1 AS VARCHAR(255))||',') AS instr_res
    FROM (
            SELECT   4 AS dato1 FROM systables WHERE tabid = 1 UNION
            SELECT  44 AS dato1 FROM systables WHERE tabid = 1 UNION
            SELECT 444 AS dato1 FROM systables WHERE tabid = 1
    )
    WHERE dato1 IN (444,44,4)
    ORDER BY 2;

  dato1   instr_res

    444           1
     44           5
      4           8

Another way is using the CHARINDEX function, also available since 11.70. Be aware that the order of the arguments is reverse; first pass the substring to look for and then the source string. The plus side of using CHARINDEX is that the cast is not required.

SELECT
        dato1,
        CHARINDEX(','||dato1||',', ',444,4,44,') AS charindex_res
FROM (
        SELECT   4 AS dato1 FROM systables WHERE tabid = 1 UNION
        SELECT  44 AS dato1 FROM systables WHERE tabid = 1 UNION
        SELECT 444 AS dato1 FROM systables WHERE tabid = 1
)
WHERE dato1 IN (444,4,44)
ORDER BY 2;

  dato1 charindex_res

    444             1
      4             5
     44             7

When using older versions of Informix that don’t have INSTR one can use DECODE:

SELECT
    dato1
FROM (
    SELECT 4 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 2 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 1 AS dato1 FROM systables WHERE tabid = 1 UNION
    SELECT 3 AS dato1 FROM systables WHERE tabid = 1
)
WHERE dato1 IN (4,2,1,3)
ORDER BY DECODE(
            dato1, 
            4, 1,   
            2, 2, 
            1, 3, 
            3, 4
);

¡Everything understood perfectly! Then I understand that the order by works in a sencond step. First, You get the result of the query and the you order using the values of the sentence CAST(dato1 AS VARCHAR(255))). Thank you very much! — Marquake, Sep 29 '15 at 11:38

ORDER BY Values especified in clausule "IN()" BUT in Generics SQL and Informix

3 Answers3