Home » SQL & PL/SQL » SQL & PL/SQL » retrieve single record row (8.1.7.0.0)
retrieve single record row [message #318903] Thu, 08 May 2008 07:25 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

I have the following data.



create table sr_ro_lub
(
SR_RO_NO                                 VARCHAR2(20),
SR_POS_CD                                VARCHAR2(8));


insert into sr_ro_lub
(SR_RO_NO,SR_POS_CD)
values
('01','CW');

insert into sr_ro_lub
(SR_RO_NO,SR_POS_CD)
values
('01','PDI');

insert into sr_ro_lub
(SR_RO_NO,SR_POS_CD)
values
('02','CW');

insert into sr_ro_lub
(SR_RO_NO,SR_POS_CD)
values
('03','CW');


now i want the following output
sr_ro_no      sr_pos_cd
02            CW
03            CW


means i want to display only those data that contain 'CW" and contain single record entry not more than one record against one sr_ro_no.
Re: retrieve single record row [message #318909 is a reply to message #318903] Thu, 08 May 2008 07:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Nice to see that you have formatted your post but you have not posted what you have tried so far. Check this link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2065777

Regards

Raj
Re: retrieve single record row [message #318941 is a reply to message #318903] Thu, 08 May 2008 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
COUNT in its analytic form may help you.

Regards
Michel
Re: retrieve single record row [message #319105 is a reply to message #318903] Fri, 09 May 2008 01:39 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
First of all thank you very much for your reply and i really appreciate your efforts, I have tried by using COUNT in its analytic form but did not get my desire output,let me repeat my question in details . I just want to know that how can i display those records that contains only single record against each sr_ro_no with sr_pos_cd='CW'.

means
First system retrieve those records that contains only single record against sr_ro_no (repair Order No) .
Second check if sr_pos_cd='CW' against sr_ro_no (repair Order No) then show the record.

regards

Zuhair
Re: retrieve single record row [message #319107 is a reply to message #319105] Fri, 09 May 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have tried by using COUNT in its analytic form but did not get my desire output

Post what you tried.

Quote:
I just want to know that how can i display those records that contains only single record against each sr_ro_no with sr_pos_cd='CW'.

Yes, use COUNT in its analytic form.

Regards
Michel
Re: retrieve single record row [message #319109 is a reply to message #319105] Fri, 09 May 2008 01:47 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Nevermind, Michel said the same thing prior to me.

[Updated on: Fri, 09 May 2008 01:48]

Report message to a moderator

Re: retrieve single record row [message #319111 is a reply to message #318903] Fri, 09 May 2008 01:53 Go to previous messageGo to next message
irfan.ocp
Messages: 29
Registered: February 2008
Location: Karachi, Pakistan
Junior Member
Hi Friends

@gozuhair
Well dear i think DISTINCT will also help you to get the Single Row.

Best Regards,
Muhammad Irfan Bakali
Re: retrieve single record row [message #319114 is a reply to message #318903] Fri, 09 May 2008 01:59 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
SQL> select count(*) from sr_ro_lub;

 COUNT(*)
---------
    25937

  1  select count(*) from sr_ro_lub
  2* where sr_pos_cd='CW'
SQL> /

 COUNT(*)
---------
      384

SQL> ed
Wrote file afiedt.buf

  1  select count(distinct sr_pos_cd) from sr_ro_lub
  2* where sr_pos_cd='CW'
SQL> /

COUNT(DISTINCTSR_POS_CD)
------------------------
                       1


SELECT sr_ro_no, sr_pos_cd,
   COUNT(*) OVER (ORDER BY sr_pos_cd) AS mov_count FROM sr_ro_lub


I know that I was using wrong select statement of analytic function because i dont have a good idea about it .
Re: retrieve single record row [message #319118 is a reply to message #319114] Fri, 09 May 2008 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
First system retrieve those records that contains only single record against sr_ro_no

So you partition by sr_ro_no and count the rows in each of these partitions. (There is no order you want to count all rows, by partition.)

Regards
Michel
Re: retrieve single record row [message #319124 is a reply to message #318903] Fri, 09 May 2008 02:11 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear Micheal

If you dont mind Can u please make a query of this
Re: retrieve single record row [message #319130 is a reply to message #319124] Fri, 09 May 2008 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First take your last query and make the modification I suggest in my last post and see what you have.
Once you see that, the final step is obvious.

Regards
Michel
Re: retrieve single record row [message #319132 is a reply to message #318903] Fri, 09 May 2008 02:21 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
ok let my try
Re: retrieve single record row [message #319135 is a reply to message #318903] Fri, 09 May 2008 02:27 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
I have tried by using below query but didnt find my desire result.

SELECT sr_ro_no, sr_pos_cd,
   COUNT(*) OVER (ORDER BY to_number(sr_ro_no)) AS mov_count FROM sr_ro_lub
where sr_pos_cd='CW'


current result look like this


SR_RO_NO             SR_POS_C MOV_COUNT
-------------------- -------- ---------
7579                 CW             316
7687                 CW             317
7704                 CW             318
7720                 CW             319
7734                 CW             320
7750                 CW             321
7828                 CW             322
7896                 CW             323
7959                 CW             324
8021                 CW             325
812                  CW             326
8173                 CW             327
822                  CW             328
8248                 CW             329
829                  CW             330
8321                 CW             331
8452                 CW             332
8456                 CW             333


Re: retrieve single record row [message #319137 is a reply to message #318903] Fri, 09 May 2008 02:30 Go to previous messageGo to next message
irfan.ocp
Messages: 29
Registered: February 2008
Location: Karachi, Pakistan
Junior Member
WITH YOUR LAST POST DATA KINDLY AGAIN SHOW your DESIRE REQUIRED


SR_RO_NO             SR_POS_C MOV_COUNT
-------------------- -------- ---------
7579                 CW             316
7687                 CW             317
7704                 CW             318
7720                 CW             319
7734                 CW             320
7750                 CW             321
7828                 CW             322
7896                 CW             323
7959                 CW             324
8021                 CW             325
812                  CW             326
8173                 CW             327
822                  CW             328
8248                 CW             329
829                  CW             330
8321                 CW             331
8452                 CW             332
8456                 CW             333




Best Regards,
Muhammad Irfan Bakali.
Re: retrieve single record row [message #319141 is a reply to message #318903] Fri, 09 May 2008 02:45 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
no its not because if i add one more record against sr_ro_no like this
insert into sr_ro_lub
(
sr_ro_no,sr_pos_cd)
values
('7579','FFS');


Then according to my needs sr_ro_no =" 7579 " eliminates because we input 2 record against same ro no first we insert the below record

insert into sr_ro_lub
(
sr_ro_no,sr_pos_cd)
values
('7579','CW');

then we insert
insert into sr_ro_lub
(
sr_ro_no,sr_pos_cd)
values
('7579','FFS');


so we have two record against same sr_ro_no and therefore according to my needs i want only single record for each rsr_ro_no with sr_pos_cd='CW'
Re: retrieve single record row [message #319142 is a reply to message #319135] Fri, 09 May 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel
So you partition by sr_ro_no and count the rows in each of these partitions. (There is no order you want to count all rows, by partition.)

In your query, I see no "partition" but I see "order".

@irfan.ocp
Don't post in UPPER CASE.

Regards
Michel
Re: retrieve single record row [message #319144 is a reply to message #318903] Fri, 09 May 2008 02:50 Go to previous message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
I think first of all i should study "analytic function" then see your guidence.

Previous Topic: Trigger created with error
Next Topic: Decode statement
Goto Forum:
  


Current Time: Sat Dec 10 17:02:27 CST 2016

Total time taken to generate the page: 0.07149 seconds