POSTGRESQL Subquery with a Order by Function does not return values

Question

I have the following code that does not return values to the select because of my order by (Function)

Select sub.enrollmentseqnumemp ,sub.membercodedep,a.Subscriber_ID
    From elan_staging.check_register_1 a
        Left join (
            Select enrollmentseqnumemp ,membercodedep
            from elan.elig
            ORDER BY public.idx(array['e','s','1','2','3','4','5'],membercodedep) Limit 1)  sub
    On sub.enrollmentseqnumemp=a.Subscriber_ID
    

    |  enrollmentseqnumemp  |  membercodedep  |  Subscriber_ID  |       
    |:----------------------|:----------------|:----------------|
    |       [null]          |      [null]     |    "462852"     |
    |       [null]          |      [null]     |    "462852"     |
    |       [null]          |      [null]     |    "407742"     |

If I run it without the Order By function, it works correctly

         Select sub.enrollmentseqnumemp 
         ,sub.membercodedep,a.Subscriber_ID
         From elan_staging.check_register_1 a
            Left join (
               Select enrollmentseqnumemp ,membercodedep
               from elan.elig
               ORDER BY 1) sub
         On sub.enrollmentseqnumemp=a.Subscriber_ID 
         Limit 1


    |  enrollmentseqnumemp  |  membercodedep  |  Subscriber_ID  |       
    |:----------------------|:----------------|:----------------|
    |       111111          |        e        |    "462852"     |
    |       222222          |        3        |    "462852"     |
    |       333333          |        s        |    "407742"     |

Code for the function from the Postgres snippets repository:

    CREATE FUNCTION idx(anyarray varchar (1) ARRAY[4], anyelement varchar (1))
        RETURNS int AS 
    $$
    SELECT i FROM (
            SELECT generate_series(array_lower($1,1),array_upper($1,1))
        ) g(i)
        WHERE $1[i] = $2
        LIMIT 1;
    $$ LANGUAGE sql IMMUTABLE;

Is there a way to fix it so that it returns the values?

Answer 1

The first query as you have written it can return non-NULLs out of elig only for the one row with globally smallest value of public.idx(...). If you want values for the smallest public.idx(...) within each enrollmentseqnumemp, you could use distinct on, like :

Select sub.enrollmentseqnumemp ,sub.membercodedep,a.Subscriber_ID
    From check_register_1 a
        Left join (
            Select distinct(enrollmentseqnumemp) enrollmentseqnumemp, membercodedep, public.idx(array['e','s','1','2','3','4','5'],membercodedep)
            from elig
            ORDER BY enrollmentseqnumemp, public.idx(array['e','s','1','2','3','4','5'],membercodedep))  sub
    On sub.enrollmentseqnumemp=a.Subscriber_ID;