SQL ORDER BY issue

Question

Duplicate of this question, which itself cross-references 3 other minor variants of the same question.

---

Hi all,

I am sorry but i am putting my question as a new question in this post.I need all your help very much now and i appreciate your help very much

SELECT *
    FROM (SELECT distinct gl.group_id,
                 gl.group_name,
                 gl.group_description,
                 gl.status_code,
                 gl.member_count,
                 (SELECT grpp.group_name
                      FROM test_group_relationship grel JOIN test_group 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   test_group AS gl
             WHERE  gl.group_org_id   = '3909'
               AND (gl.group_name_key LIKE '%GROUP%')
         ) AS data_set 
    ORDER BY DECODE(:sort_key,
                      'name',            'constant',
                      'description',     group_description_key,
                      'memberCount',     LPAD(member_count, 4),
                      'status',          LPAD(status_code, 4),
                      'parentGroupName', parent_group_name
               )NULLS FIRST,
         UPPER(SUBSTR(group_name, 1, 1)),
         SUBSTR(group_name, 1, 1) DESC,
         UPPER(group_name),
         group_name DESC,
         group_name_key, 
         ;

My question still remains the similar.I want dynamic sorting to be done based on the :sort_key variable which has one of the various options in the decode everytime to sort.

When,sort_key :='name'.As we can see it evaluates to a constant and then the remaining columns in the ORDER BY clause.

Now,when variable sort_key:= 'description' or 'memberCount'....each of the values, then the ORDER BY LOGIC is different for each of them.

As an example,when 'description' is selected-the sorting in the ORDER BY cluase should do as it does for 'name'.

Example like this:-

 UPPER(SUBSTR(group_description, 1, 1)),
             SUBSTR(group_description, 1, 1) DESC,
             UPPER(group_description),
             group_description DESC,
             group_description_key,

In brief,each of the options that the sort_key variable gets-the ORDER BY logic is different and i have to implement it in the same SQL only now.

If its possible with order by CASE with all the options-can you please help me.I need your help very much

Answer 1

If I understand you correctly, you are asking if it is possible to vary the whole ORDER BY list dynamically based on a bound parameter, including ASC/DESC variations, etc.

Your best bet is to run different queries with different ORDER BYs depending on how you want to sort. It is much simpler and will run faster.

If you really want to do it the way you are asking, you can do it to a certain extent using the same technique you are already using with the ORDER BY DECODE(choice1, expression1, choice2, expression2,...), except that the expressions would have to be a lot more complex. Each expression would have to produce a concatenated string of fixed-size fields in order of sort priority, for example:

ORDER BY DECODE('description',     UPPER(SUBSTR(group_description, 1, 1))
                                || SUBSTR(group_description, 1, 1) 
                                || RPAD(UPPER(group_description),40)
                                || RPAD(group_description_key,10)

                ,'name',           UPPER(SUBSTR(group_name, 1, 1)),
                                || LPAD(1000-ASCII(group_name)), -- first char DESC
                                || RPAD(UPPER(group_name),20)
                                || RPAD(group_name_key,10)

Also, it would be difficult to mix ASC and DESC fields. You can switch direction for numeric fields using something like LPAD(1000000 - n,7) where the 1000000 is a number higher than any possible n. You can also play some other tricks like I did in my example with the first char of group_name.

This technique will prevent the database from using indexes to speed up the ordering (unless you add some crazy function-based indexes).

Again, I recommend you abandon this approach and just send the database a whole different query depending on your desired ORDER BY clause. If you are using PL/SQL, you can also use dynamic SQL to build the query with the desired ORDER BY clause and execute that.

EDIT: My PL/SQL is a little rusty but here is how you can do it in PL/SQL with dynamic SQL.

FUNCTION QueryGroups(sort_key in varchar2) RETURN REF CURSOR
IS
   sql_block VARCHAR2(2000);
   order_by VARCHAR2(2000);
   ret REF CURSOR;
BEGIN
   order_by :=
     CASE sort_key
       WHEN 'name'
         THEN  q'{UPPER(SUBSTR(group_name, 1, 1)),
                  SUBSTR(group_name, 1, 1) DESC,
                  UPPER(group_name),
                  group_name DESC,
                  group_name_key}';
       WHEN 'description'
         THEN  q'{UPPER(SUBSTR(group_description, 1, 1)),
                  SUBSTR(group_description, 1, 1) DESC,
                  UPPER(group_description),
                  group_description DESC,
                  group_description_key}';
     END CASE;

     sql_block = q'{SELECT *
                FROM (SELECT distinct gl.group_id,
                 gl.group_name,
                 gl.group_description,
                 gl.status_code,
                 gl.member_count,
                 (SELECT grpp.group_name
                      FROM test_group_relationship grel JOIN test_group 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   test_group AS gl
             WHERE  gl.group_org_id   = '3909'
               AND (gl.group_name_key LIKE '%GROUP%')
         ) AS data_set }' || order_by;

     OPEN ret for sql_block;
     return ret;
END QueryGroups;