SQL order by Issue-Continued

Question

This issue is a continuation of my earlier query. It's still not working. It's about an ORDER BY clause. I am trying to sort using a variable called "sortby".

Here, now the ORDER BY clause is selected as a separate column using the DECODE() function (as suggested in the answer by @devio in the original version of this question).

Let’s say sortby = ‘memberCount’ in this case, I passed it as first argument in decode(); memberCount is a COLUMN in the grptest table.

select distinct gl.group_id,
       decode('memberCount', 'name',        gl.group_name_key,
                             'description', gl.group_description_key,
                             'memberCount', gl.member_count)
             as p_sortby,
       gl.group_name,
       gl.group_description,
       gl.status_code,
       gl.member_count,
       (select grpp.group_name
           from grptest_relationship grel join grptest grpp
                   on grel.parent_group_id = grpp.group_id
           where grel.child_group_id = gl.group_id) as parent_group_name,
       gl.group_name_key,
       gl.group_description_key
   from grptest gl
   where gl.group_org_id = '3909'
     and (gl.group_name_key like '%' || 'GROUP' || '%')
order by 2;

It doesn’t work.

But if I pass ‘name’ as first argument in decode above, it works. That’s my original issue about why it doesn’t apply on memberCount.

Answer 1

I know that

order by p_sortby

wont work, but could you try

order by decode('memberCount', 
  'name', gl.group_name_key,
  'description', gl.group_description_key, 
  'memberCount', gl.member_count)

EDIT:

I remembered another way:

select * from (
  select column1, decode(....) as column2, .... from table1
) t1
order by 2

and this way might even be a faster one

Answer 2

I commented:

What is the error you get? Or what is the erroneous behaviour you get? In my adaptation of your question to my database, I had to ensure that the numeric column was converted to a character type before the DECODE() was acceptable - the other two columns were character columns. With that done, and with the minor issue that sorting numbers alphabetically places '8' after '79' and before '80', I got an appropriate result.

Rohit asked:

Thanks for the inputs. I guess I am confused about the minor issue you mentioned that "that sorting numbers alphabetically places '8' after '79' and before '80'". I couldn't get what is the thing here? Also, could you please help in my query of how "to ensure that the numeric column was converted to a character type before the DECODE() was acceptable". Can you please modify my query above in this respect?

---

The table I used is for the 'table of elements':

-- Tables for storing information about chemical elements and chemical compounds
-- See: http://www.webelements.com/ for elements.
-- See: http://ie.lbl.gov/education/isotopes.htm for isotopes.

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL UNIQUE
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE,
    name            CHAR(20) NOT NULL UNIQUE,
    atomic_weight   DECIMAL(8,4) NOT NULL,
    stable          CHAR(1) DEFAULT 'Y' NOT NULL
                    CHECK (stable IN ('Y', 'N'))
);

It's an interesting table because it has three genuine candidate keys (atomic number, name and symbol are each unique), and depending on context (isotopes vs chemicals), you are better off using atomic number or symbol as the joining key.

The queries I used were:

select decode('atomic_number',
                    'name',          name,
                    'symbol',        symbol,
                    'atomic_number', atomic_number||''),
        name, symbol, atomic_number
    from elements
    order by 1;

select decode('name',
                    'name',          name,
                    'symbol',        symbol,
                    'atomic_number', atomic_number||''),
        name, symbol, atomic_number
    from elements
    order by 1;

select decode('symbol',
                    'name',          name,
                    'symbol',        symbol,
                    'atomic_number', atomic_number||''),
        name, symbol, atomic_number
    from elements
    order by 1;

These demonstrated the three orderings - by symbol, by name, and by atomic number.

Part of the result set for the atomic number ordering was:

77      Iridium         Ir      77
78      Platinum        Pt      78
79      Gold            Au      79
8       Oxygen          O       8
80      Mercury         Hg      80
81      Thallium        Tl      81

Because the atomic number was coerced into a string, the sort was in string order, and when regarded as a string, '8' appears after '79' and before '80', as shown. One way of avoiding that problem would be:

select decode('atomic_number',
                    'name',          name,
                    'symbol',        symbol,
                    'atomic_number', lpad(atomic_number, 3)),
        name, symbol, atomic_number
    from elements
    order by 1;

Producing the following (which, though it isn't obvious, has an extra blank at the start of the first column):

 77     Iridium         Ir      77
 78     Platinum        Pt      78
 79     Gold            Au      79
 80     Mercury         Hg      80
 81     Thallium        Tl      81
 82     Lead            Pb      82

This uses the knowledge that space precedes any digit in the (ASCII, Latin-1, Unicode) sort sequence, and that atomic numbers are not more than 3 digits. Alternatively, I could have used 'LPAD(atomic_number, 3, '0')' to zero-pad the data. I tested with IBM Informix Dynamic Server (IDS) 11.50.FC3W2 on Solaris 10. IDS is very tolerant of type mismatches and automatically converts the atomic_number argument to LPAD into a string. Other DBMS may not be so tolerant; you'd have to explicitly cast the value.

Going back to the question...

Assuming memberCount is a numeric column and the values are not more than 4 digits long (adjust appropriately if they are longer), the query can be written:

select distinct gl.group_id,
       decode('memberCount', 'name',        gl.group_name_key,
                             'description', gl.group_description_key,
                             'memberCount', LPAD(gl.member_count, 4))
             as p_sortby,
       gl.group_name,
       gl.group_description,
       gl.status_code,
       gl.member_count,
       (select grpp.group_name
           from grptest_relationship grel join grptest grpp
                   on grel.parent_group_id = grpp.group_id
           where grel.child_group_id = gl.group_id) as parent_group_name,
       gl.group_name_key,
       gl.group_description_key
   from grptest gl
   where gl.group_org_id = '3909'
     and (gl.group_name_key like '%' || 'GROUP' || '%')
order by 2;

Or you might need:

LPAD(CAST(memberCount AS CHAR(4)), 4)

or some other slightly DBMS-specific incantation that achieves the same general effect.

Since you didn't provide a schema (much less sample data) for the query, I don't have your table in my database, so I can't demo your query working