Home » SQL & PL/SQL » SQL & PL/SQL » Find unique and sorted field value
Find unique and sorted field value [message #654863] Tue, 16 August 2016 04:51 Go to next message
amarbose
Messages: 21
Registered: May 2011
Junior Member
Hi,
A column named input contains the following values in a table.The column Transformed output contains unique value of the column named input and in sorted manner.

Sample output is present in column Transformed Output.

[b]Input[/b]                                            [b]Transformed output[/b]
G25,G25,G25,G25,G28,G28,G29,G25,G25	          G25,G28,G29
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26	  G25,G26,G28,G29
How can this be achieve using SQL
Re: Find unique and sorted field value [message #654869 is a reply to message #654863] Tue, 16 August 2016 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This has already been posted here several, please do search.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Find unique and sorted field value [message #654872 is a reply to message #654869] Tue, 16 August 2016 06:01 Go to previous messageGo to next message
amarbose
Messages: 21
Registered: May 2011
Junior Member
Please share me those links if possible.
Re: Find unique and sorted field value [message #654873 is a reply to message #654872] Tue, 16 August 2016 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please do post what is requested and use the Search link above, if possible.

Re: Find unique and sorted field value [message #654913 is a reply to message #654872] Tue, 16 August 2016 19:25 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Here is a link to a nice option within a thread of various options.

http://www.orafaq.com/forum/mv/msg/201410/653444/#msg_653444

The following adapts the above to your situation.

SCOTT@orcl_12.1.0.2.0> select * from a_table
  2  /

INPUT
-------------------------------------------
G25,G25,G25,G25,G28,G28,G29,G25,G25
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26

2 rows selected.

SCOTT@orcl_12.1.0.2.0> select replace (column_value, ' ', ',') transformed_output
  2  from   a_table,
  3  	    xmltable (('xs:string (distinct-values (("' || replace (input, ',', '","') || '")))'))
  4  /

TRANSFORMED_OUTPUT
--------------------------------------------------------------------------------
G25,G28,G29
G26,G28,G25,G29

2 rows selected.

[Updated on: Tue, 16 August 2016 19:27]

Report message to a moderator

Re: Find unique and sorted field value [message #654931 is a reply to message #654872] Wed, 17 August 2016 02:51 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
One another way to do this without xmltable

with test as
 (select 'G25,G25,G25,G25,G28,G28,G29,G25,G25' col,
         regexp_count('G25,G25,G25,G25,G28,G28,G29,G25,G25', ',') + 1 cnt
    from dual
  union all
  select 'G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26' col,
         regexp_count('G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26', ',') + 1 cnt
    from dual)
select col input,
       listagg(new_val, ',') within group(order by new_val) transformed_output
  from (select distinct col,
                        to_char(regexp_substr(col || ',',
                                              '([^,]*),|$',
                                              1,
                                              rnk,
                                              null,
                                              1)) new_val
          from (select col, rank() over(partition by col order by rownum) rnk
                
                  from test,
                       table(cast(multiset (select level flg
                                     from dual
                                   connect by level <= cnt) as
                                  sys.odcinumberlist))))

 group by col;


Result:

INPUT                                       TRANSFORMED_OUTPUT
------------------------------------------- --------------------------------------------------------------------------------
G25,G25,G25,G25,G28,G28,G29,G25,G25         G25,G28,G29
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26 G25,G26,G28,G29
Re: Find unique and sorted field value [message #654932 is a reply to message #654931] Wed, 17 August 2016 03:10 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you use RANK you may lose some rows, use ROW_NUMBER instead.

A slightly simpler solution based on one in the link:
SQL> with
  2    test as (
  3      select 'G25,G25,G25,G25,G28,G28,G29,G25,G25' val
  4      from dual
  5      union all
  6      select 'G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26' val
  7      from dual
  8    ),
  9    data as (
 10      select val, row_number() over (order by null) rn
 11      from test
 12    )
 13  select val input, listagg(word,',') within group (order by word) output
 14  from ( select distinct val, rn, regexp_substr(val, '[^,]+', 1, column_value) word
 15         from data,
 16              table(cast(multiset(select level from dual
 17                                  connect by level <= regexp_count(val,',')+1)
 18                    as sys.odciNumberList)) )
 19  group by rn, val
 20  /
INPUT                                       OUTPUT
------------------------------------------- --------------------------------------------------
G25,G25,G25,G25,G28,G28,G29,G25,G25         G25,G28,G29
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26 G25,G26,G28,G29

2 rows selected.
Re: Find unique and sorted field value [message #654941 is a reply to message #654932] Wed, 17 August 2016 04:41 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
You are right Michel about ROW_NUMBER()

but i used rank() over(partition by col order by rownum)

so we would not loose any record.
Re: Find unique and sorted field value [message #654942 is a reply to message #654941] Wed, 17 August 2016 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
See:
SQL>    with test as
  2   (select 'G25,G25,G25,G25,G28,G28,G29,G25,G25' col,
  3           regexp_count('G25,G25,G25,G25,G28,G28,G29,G25,G25', ',') + 1 cnt
  4      from dual
  5    union all
  6    select 'G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26' col,
  7           regexp_count('G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26', ',') + 1 cnt
  8      from dual
  9    union all
 10    select 'G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26' col,
 11           regexp_count('G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26', ',') + 1 cnt
 12      from dual)
 13  select col input,
 14         listagg(new_val, ',') within group(order by new_val) transformed_output
 15    from (select distinct col,
 16                          to_char(regexp_substr(col || ',',
 17                                                '([^,]*),|$',
 18                                                1,
 19                                                rnk,
 20                                                null,
 21                                                1)) new_val
 22            from (select col, rank() over(partition by col order by rownum) rnk
 23                    from test,
 24                         table(cast(multiset (select level flg
 25                                       from dual
 26                                     connect by level <= cnt) as
 27                                    sys.odcinumberlist))))
 28   group by col;
INPUT                                       TRANSFORMED_OUTPUT
------------------------------------------- --------------------------------------------------
G25,G25,G25,G25,G28,G28,G29,G25,G25         G25,G28,G29
G26,G26,G26,G26,G28,G25,G28,G29,G26,G25,G26 G25,G26,G28,G29

2 rows selected.
3 rows input, 2 output.
Re: Find unique and sorted field value [message #654945 is a reply to message #654942] Wed, 17 August 2016 06:12 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
Oh Yes, right.

I assumed by missing rows is missing values in TRANSFORMED_OUTPUT.

You are right, my query will give just the distinct results (no repetition of duplicate rows from the input).
Re: Find unique and sorted field value [message #654946 is a reply to message #654945] Wed, 17 August 2016 06:28 Go to previous message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The ORDER BY is ROW_NUMBER/RANK is irrelevant here as you don't care of the order of the rows when you number them, they just need to have a distinct number whatever this later one is.


Previous Topic: keep only numeric values in a string
Next Topic: PL/SQL: ORA-00942: table or view does not exist
Goto Forum:
  


Current Time: Sun Jun 13 16:34:11 CDT 2021