Home » SQL & PL/SQL » SQL & PL/SQL » Selecting the correct record from the table (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production,TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production, Windows)
Selecting the correct record from the table [message #596842] Thu, 26 September 2013 12:36 Go to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
Hi ,

I have a table with following structure:

CREATE TABLE ID_comments
(
  ID              CHAR(10 BYTE)               NOT NULL,
  S_COMMENTS      VARCHAR2(255 BYTE),
  P_COMMENTS      VARCHAR2(255 BYTE),
  C_COMMENTS      VARCHAR2(255 BYTE)
);


For each Id, I can have multiple records.

Below is the insert script of one of the ID:

Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE','');
Insert into ID_comments values ('0813654254','','JR','');
Insert into ID_comments values ('0813654254','Single 08136536066 x 6, 8pp','','');
Insert into ID_comments values ('0813654254','72 6pks short per Denise','','');
Insert into ID_comments values ('0813654290','','JR multiple','');
Insert into ID_comments values ('0813654290','','JR multiple','');
Insert into ID_comments values ('0813654290','','JR MULTIPLE','');
Insert into ID_comments values ('0813654290','','JR','');
Insert into ID_comments values ('0813654290','Multiple 08136536066 x 6, 8pp','','');
Insert into ID_comments values ('0813654290','72 6pks short','','ABC');

commit;


Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.

Please let me know if more information is required.
Re: Selecting the correct record from the table [message #596843 is a reply to message #596842] Thu, 26 September 2013 12:37 Go to previous messageGo to next message
pablolee
Messages: 2590
Registered: May 2007
Location: Scotland
Senior Member
What if there are two rows that meet the requirements?
Re: Selecting the correct record from the table [message #596844 is a reply to message #596842] Thu, 26 September 2013 12:38 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
what is expected/desired result set from sample data above (& why)?
icon5.gif  Re: Selecting the correct record from the table [message #596846 is a reply to message #596842] Thu, 26 September 2013 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Question
SQL> with 
  2    data as (
  3      select ID, S_COMMENTS, P_COMMENTS, C_COMMENTS,
  4             row_number() over 
  5               (partition by id 
  6                order by nvl2(S_COMMENTS, 1, 0)+nvl2(P_COMMENTS, 1, 0)+nvl2(C_COMMENTS, 1, 0) desc)
  7               rn
  8      from ID_comments
  9    )
 10  select ID, S_COMMENTS, P_COMMENTS, C_COMMENTS
 11  from data
 12  where rn = 1
 13  /
ID         S_COMMENTS           P_COMMENTS           C_COMMENTS
---------- -------------------- -------------------- --------------------
0813654254                      JR/0813653606 single
0813654290 72 6pks short                             ABC

2 rows selected.

Question

Re: Selecting the correct record from the table [message #596847 is a reply to message #596844] Thu, 26 September 2013 13:11 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
BlackSwan wrote on Thu, 26 September 2013 17:38
what is expected/desired result set from sample data above (& why)?


The desired output is as below:


ID S_COMMENTS P_COMMENTS C_COMMENTS
---------- -------------------------------- -------------------- --------------------
0813654254 Single 08136536066 x 6, 8pp JR/0813653606 single
0813654290 Multiple 08136536066 x 6, 8pp JR MULTIPLE ABC

For Id-0813654254 :
There are two "not null" values for S_comments-pick any one.
There are 4 "not null" values of P_comments,Pick anyone.
There are no "not null" values, pick anyone.

For ID-0813654290:
There are two "not null" values for S_comments-pick any one.
There are 4 "not null" values of P_comments,Pick anyone.
There is one "not null" values, pick that one.

The requirement is to pick as many "not null" column as we can. In case we get multiple such records , we can pick anyone randomly(may be max(rowid))
icon3.gif  Re: Selecting the correct record from the table [message #596854 is a reply to message #596847] Thu, 26 September 2013 13:36 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select id,
  2         max(s_comments) s_comments,
  3         max(p_comments) p_comments,
  4         max(c_comments) c_comments
  5  from ID_comments
  6  group by id
  7  /
ID         S_COMMENTS                     P_COMMENTS                 C_COMMENTS
---------- ------------------------------ -------------------------- --------------------------
0813654254 Single 08136536066 x 6, 8pp    JR/0813653606 single
0813654290 Multiple 08136536066 x 6, 8pp  JR multiple                ABC

2 rows selected.

Re: Selecting the correct record from the table [message #596864 is a reply to message #596854] Thu, 26 September 2013 15:33 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
Oh my goodness... Could not think of such an easy solution...

Anyways thanks a lot to all of you for your prompt help and time...
Re: Selecting the correct record from the table [message #596936 is a reply to message #596854] Fri, 27 September 2013 11:42 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
Hi,

Need one more help.

How can I concat all the 'not null' records in a column for an ID to display it in one row.

the desired output will be like :

ISBN S_COMMENTS P_COMMENTS C_COMMENTS
------------------------------------------------------------------------------------------
0813654290 Multiple 08136536066 x 6|72 6pks short JR multiple|JR multiple|JR MULTIPLE|JR

For now we can assume that concatenated length would not exceed varchar2(5000).

Re: Selecting the correct record from the table [message #596937 is a reply to message #596936] Fri, 27 September 2013 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
>For now we can assume that concatenated length would not exceed varchar2(5000).
interesting; since the actual limit is 4000
icon3.gif  Re: Selecting the correct record from the table [message #596938 is a reply to message #596936] Fri, 27 September 2013 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Replace MAX by STRAGG

Re: Selecting the correct record from the table [message #596939 is a reply to message #596938] Fri, 27 September 2013 12:07 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
I am getting below error when I used STRAGG:


SELECT ID,
       STRAGG(S_COMMENTS) "S_COMMENTS",
       STRAGG(P_COMMENTS) "P_COMMENTS",
       STRAGG(C_COMMENTS) "C_COMMENTS"
  FROM ID_COMMENTS 
        group by ID


ORA-00904: "STRAGG": invalid identifier
icon2.gif  Re: Selecting the correct record from the table [message #596940 is a reply to message #596939] Fri, 27 September 2013 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry it is LISTAGG (STRAGG is the same function written by T. Kyte in previous versions of Oracle)
You have to read the link I posted as LISTAGG is a little bit more complex than MAX.
Come back if you can't do it (and come back with the solution if you can Smile ).

Re: Selecting the correct record from the table [message #596942 is a reply to message #596940] Fri, 27 September 2013 13:13 Go to previous messageGo to next message
gupta27
Messages: 28
Registered: August 2013
Location: Delhi
Junior Member
SELECT ID,
       LISTAGG(S_COMMENTS,' || ') within group (order by s_comments) S_COMMENTS,
       LISTAGG(P_COMMENTS,' || ') within group (order by p_comments) P_COMMENTS,
       LISTAGG(C_COMMENTS,' || ') within group (order by c_comments) C_COMMENTS
  FROM ID_COMMENTS 
        group by ID


ID S_COMMENTS P_COMMENTS C_COMMENTS
0813654254 72 6pks short per Denise || Single 08136536066 x 6, 8pp JR || JR/0813653606 SINGLE || JR/0813653606 single || JR/0813653606 single

That was one wonderful function. Thanks a lot for your help and time.
icon14.gif  Re: Selecting the correct record from the table [message #596943 is a reply to message #596942] Fri, 27 September 2013 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Re: Selecting the correct record from the table [message #596976 is a reply to message #596942] Sat, 28 September 2013 07:26 Go to previous message
Solomon Yakobson
Messages: 1794
Registered: January 2010
Senior Member
Just keep in mind LISTAGG result is VARCHAR2/RAW and therefore is limited to 4000 bytes. You can use XMLAGG (which supports CLOB) if result can exceed 4000 bytes.

SY.
Previous Topic: complex constraint
Next Topic: Package variables and execution Error [merged 2 by jd]
Goto Forum:
  


Current Time: Sat Apr 19 22:57:55 CDT 2014

Total time taken to generate the page: 0.11864 seconds