trying to group by generic_m_ai

Question

Our current application allows to filter data by NLSSORT=generic_m_ai, I'd like to be able to group by it as well. Currently testing it with a small table, but keeping the 'case' statement similar to real query

This is how my table is created:

create table alex_collate_test
(aaa varchar(30),
bbb varchar(30), primary key(aaa, bbb))
insert into alex_collate_test values ('hi', 'bye')
insert into alex_collate_test values ('HI', 'bye')
insert into alex_collate_test values ('hi', 'BYE')
insert into alex_collate_test values ('HI', 'BYE')
insert into alex_collate_test values ('next', 'howdy')

Here's the regular group by:

select case when aaa is null or length(aaa) = 0 then '(blank) - '|| case when bbb is null then '' else bbb end  else aaa||' - '|| case when bbb is null then '' else bbb end  end mycolumn
from alex_collate_test
group by case when aaa is null or length(aaa) = 0 then '(blank) - '|| case when bbb is null then '' else bbb end  else aaa||' - '|| case when bbb is null then '' else bbb end  end

and get:

hi - BYE                                                        
HI - BYE                                                        
hi - bye                                                        
next - howdy                                                    
HI - bye 

I'm following MSSQL's 'Collate' code, and wrapping the columns in 'NLSSORT':

select NLSSORT(case when aaa is null or length(aaa) = 0 then '(blank) - '|| case when bbb is null then '' else bbb end  else aaa||' - '|| case when bbb is null then '' else bbb end  end, 'NLS_SORT=generic_m_ai') mycolumn
from alex_collate_test
group by NLSSORT(case when aaa is null or length(aaa) = 0 then '(blank) - '|| case when bbb is null then '' else bbb end  else aaa||' - '|| case when bbb is null then '' else bbb end  end , 'NLS_SORT=generic_m_ai')

And get this output:

0213021B01EF026701FE
023201FE0266025502130238026401F70267

At first it looks like hex, that I just have to convert to varchar, but if you look closer, it's not hex. It has '02' - which isn't any normal character like 'hi' or 'bye'.

My expected output, or rather what I want to get is this:

HI - BYE
next - howdy

Answer 1

NLSSORT gives you the bytes used to perform the sort using that value, and is a RAW value. So it's not directly useful.

But you can use that indirectly to achieve this. There may well be simpler methods, but using an analytic function that gives you a pseudo-column based on the sort order seems to work:

select mygroupcolumn
from (
  select mycolumn, first_value(mycolumn)
    over (partition by NLSSORT(mycolumn, 'NLS_SORT=generic_m_ai')
      order by NLSSORT(mycolumn, 'NLS_SORT=generic_m_ai')) as mygroupcolumn
  from (
    select case when aaa is null or length(aaa) = 0 then '(blank)' else aaa end
      || ' - ' || case when bbb is not null then bbb end as mycolumn
    from alex_collate_test
  )
)
group by mygroupcolumn
order by mygroupcolumn;

MYGROUPCOLUMN                                                 
---------------------------------------------------------------
HI - BYE                                                        
next - howdy                                                    

I've also simplified (IMO) the case structure, but maybe that isn't so straightforward with your real one.

Adding a number column to check the result further, on the assumption you're grouping for a reason, your query gets:

MYCOLUMN                                                          SUM(CCC)
--------------------------------------------------------------- ----------
HI - BYE                                                                 8 
HI - bye                                                                 2 
hi - BYE                                                                 4 
hi - bye                                                                 1 
next - howdy                                                            16 

And mine, with a tweak to get that ccc value out from the inner queries:

select mygroupcolumn, sum(ccc)
from (
  select mycolumn, ccc, first_value(mycolumn)
    over (partition by NLSSORT(mycolumn, 'NLS_SORT=generic_m_ai')
      order by NLSSORT(mycolumn, 'NLS_SORT=generic_m_ai')) as mygroupcolumn
  from (
    select case when aaa is null or length(aaa) = 0 then '(blank)' else aaa end
      || ' - ' || case when bbb is not null then bbb end as mycolumn, ccc
    from alex_collate_test
  )
)
group by mygroupcolumn
order by mygroupcolumn;

... gets:

MYGROUPCOLUMN                                                     SUM(CCC)
--------------------------------------------------------------- ----------
HI - BYE                                                                15 
next - howdy                                                            16 

---

I do seem to have reimplemented MIN(), kind of. @kordirko's answer is simpler; with the same data that also gives:

select min(mycolumn), sum(ccc)
from (
  select case when aaa is null or length(aaa) = 0 then '(blank)' else aaa end
    || ' - ' || case when bbb is not null then bbb end as mycolumn, ccc
  from alex_collate_test
)
group by NLSSORT(mycolumn, 'NLS_SORT=generic_m_ai')
order by NLSSORT(mycolumn, 'NLS_SORT=generic_m_ai');

MIN(MYCOLUMN)                                                     SUM(CCC)
--------------------------------------------------------------- ----------
HI - BYE                                                                15 
next - howdy                                                            16 

... so I'd probably go with that unless you need other analytic results anyway.

Answer 2

Maybe using MIN() can solve this problem

with qry as (
  select 
    case when aaa is null or length(aaa) = 0 then '(blank) - '|| 
    case when bbb is null then '' else bbb end  else aaa||' - '|| 
    case when bbb is null then '' else bbb end  
    end mycolumn 
  from alex_collate_test
)
select min( mycolumn ) mycolumn
from qry
group by NLSSORT( mycolumn, 'NLS_SORT=generic_m_ai')

Here is a link to sql fiddle: http://sqlfiddle.com/#!4/6934c/2