OraFAQ Forum: SQL & PL/SQL » Selecting the correct record from the table

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Selecting the correct record from the table [message #596842]

Thu, 26 September 2013 12:36

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

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.

Report message to a moderator

Re: Selecting the correct record from the table [message #596843 is a reply to message #596842]

Thu, 26 September 2013 12:37

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

What if there are two rows that meet the requirements?

Report message to a moderator

Re: Selecting the correct record from the table [message #596844 is a reply to message #596842]

Thu, 26 September 2013 12:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what is expected/desired result set from sample data above (& why)?

Report message to a moderator

Re: Selecting the correct record from the table [message #596846 is a reply to message #596842]

Thu, 26 September 2013 13:01

Michel Cadot
Messages: 68645
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

Report message to a moderator

Re: Selecting the correct record from the table [message #596847 is a reply to message #596844]

Thu, 26 September 2013 13:11

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

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))

Report message to a moderator

Re: Selecting the correct record from the table [message #596854 is a reply to message #596847]

Thu, 26 September 2013 13:36

Michel Cadot
Messages: 68645
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.

Report message to a moderator

Re: Selecting the correct record from the table [message #596864 is a reply to message #596854]

Thu, 26 September 2013 15:33

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

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...

Report message to a moderator

Re: Selecting the correct record from the table [message #596936 is a reply to message #596854]

Fri, 27 September 2013 11:42

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

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).

Report message to a moderator

Re: Selecting the correct record from the table [message #596937 is a reply to message #596936]

Fri, 27 September 2013 11:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>For now we can assume that concatenated length would not exceed varchar2(5000).
interesting; since the actual limit is 4000

Report message to a moderator

Re: Selecting the correct record from the table [message #596938 is a reply to message #596936]

Fri, 27 September 2013 11:53

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Replace MAX by STRAGG

Report message to a moderator

Re: Selecting the correct record from the table [message #596939 is a reply to message #596938]

Fri, 27 September 2013 12:07

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

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

Report message to a moderator

Re: Selecting the correct record from the table [message #596940 is a reply to message #596939]

Fri, 27 September 2013 12:18

Michel Cadot
Messages: 68645
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

Smile

).

Report message to a moderator

Re: Selecting the correct record from the table [message #596942 is a reply to message #596940]

Fri, 27 September 2013 13:13

gupta27
Messages: 31
Registered: August 2013
Location: Delhi

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.

Report message to a moderator

Re: Selecting the correct record from the table [message #596943 is a reply to message #596942]

Fri, 27 September 2013 13:20

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the feedback.

Report message to a moderator

Re: Selecting the correct record from the table [message #596976 is a reply to message #596942]

Sat, 28 September 2013 07:26

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 02:28:51 CDT 2024