SQL Sorting using Order by

Question

Can you all please help me with this?

Presently, I have this SELECT which returns data ordered by this way

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 gl
WHERE   gl.group_org_id = '3909'
        AND gl.group_name_key like '%' || 'GROUP' || '%'
ORDER BY
        gl.group_name_key, CONVERT(gl.group_name, 'WE8EBCDIC500')

The output is below.I have tried indenting the columns to paste the data.

GROUP_NAME                         GROUP_NAME_KEY

Add Group Basic Flow               ADD GROUP BASIC FLOW
Administrative Group               ADMINISTRATIVE GROUP
Amy Group 33                       AMY GROUP 33
Amy Test Group 1                   AMY TEST GROUP 1
another add group test from matt   ANOTHER ADD GROUP TEST FROM MATT

**My Question is in the FIELD GROUP_NAME--> how can i SORT DATA using ORDER BY so that lowercase letters will be sorted before uppercase letters.

Expected output is :-

the value "another add group test from matt" has to come at the first place.This way lowercase letters are sorted first and then UPPER CASE.

---

See also:

• SQL ORDER BY Issue Continued
• PLSQL ORDER BY Issue

Answer 1

Convert the type on the field to a collation that is case sensitive and order by it asc

In your order by add

Group_Name COLLATE Latin1_General_CS_AS Asc

assuming that your characters are in english; otherwise substitute french etc.

Answer 2

Just add BINARY to your ORDER BY.

ORDER BY BINARY gl.group_name_key

You may have to use DESC otherwise upper case will come first. But then that would also sort z-a.

Answer 3

I'm not an Oracle guy, but depending on your version of Oracle, I believe that there are some session variables that will determine this for you. You can try the following:

ALTER SESSION SET nls_comp=binary;

ALTER SESSION SET nls_sort=GENERIC_M_CI;

Answer 4

try:

ORDER BY UPPER (SUBSTR (GROUP_NAME, 1, 1)), SUBSTR (GROUP_NAME, 1, 1) DESC, UPPER(GROUP_NAME), GROUP_NAME DESC;