Home » SQL & PL/SQL » SQL & PL/SQL » query (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production)
query [message #354041] Thu, 16 October 2008 03:36 Go to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
create table nw_xyz(NID number,maxmsgcnt number);

create table msg_xyz(nid number,msg varchar2(10));

INSERT INTO NW_XYZ ( NID, MAXMSGCNT ) VALUES (
1, 3);
INSERT INTO NW_XYZ ( NID, MAXMSGCNT ) VALUES (
2, 4);
COMMIT;


INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
1, 'A');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
1, 'B');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
1, 'C');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
1, 'D');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
2, 'M');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
2, 'N');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
2, 'O');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
2, 'P');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
2, 'Q');
INSERT INTO MSG_XYZ ( NID, MSG ) VALUES (
2, 'R');
COMMIT;

SELECT A.NID,MSG FROM MSG_XYZ A,NW_XYZ B
WHERE A.NID=B.NID;


nid msg
-----------
1 A
1 D
1 C
1 B
2 Q
2 P
2 O
2 N
2 R
2 M

I want to restrict the output to below given, as the number of output records against nid with the value of MAXMSGCNT in NW_XYZ table against NID

nid msg
-----------
1 A
1 D
1 C --3 records for NID 1
2 Q
2 P
2 O
2 N --3 records for NID 2
Re: query [message #354044 is a reply to message #354041] Thu, 16 October 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the test case but please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

You have to first define an order on your rows otherwise whatever will be your query you might give a different order each time you will execute it.
Then it is easy to number them (using row_number function for instance) and stop when the number exceeds the threshold.

Regards
Michel
Re: query [message #354095 is a reply to message #354041] Thu, 16 October 2008 07:14 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Try this...!

SELECT nid, msg
  FROM (SELECT a.nid, msg, b.maxmsgcnt,
               ROW_NUMBER () OVER (PARTITION BY a.nid ORDER BY a.nid) rn
          FROM msg_xyz a, nw_xyz b
         WHERE a.nid = b.nid)
 WHERE rn <= maxmsgcnt 

Re: query [message #354097 is a reply to message #354095] Thu, 16 October 2008 07:18 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member
I have used the same way. Thanks anyways.
Re: query [message #354098 is a reply to message #354095] Thu, 16 October 2008 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@spmano1983

Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


We don't care you can do it, just let OP learn to do it.

In addition, "PARTITION BY a.nid ORDER BY a.nid" is silly and the second table should not be in the inline view.

Regards
Michel
Re: query [message #354256 is a reply to message #354095] Fri, 17 October 2008 03:56 Go to previous messageGo to next message
subusona
Messages: 70
Registered: March 2005
Location: delhi
Member

Michel,

query given by spmano is working.

I have made changes to it considering your statement.i.e moving out the second table.

SELECT a.nid, msg
FROM (SELECT a.nid, msg,
ROW_NUMBER () OVER (PARTITION BY a.nid ORDER BY a.nid) rn
FROM msg_xyz a
)a, nw_xyz b
WHERE a.nid = b.nid
and rn <= b.maxmsgcnt

I do not understant why you said
"PARTITION BY a.nid ORDER BY a.nid" is silly

can you explain.What can be used instead.

Regards
Re: query [message #354259 is a reply to message #354256] Fri, 17 October 2008 04:14 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
query given by spmano is working.

This does not mean it is correct. Anyway, it should far far better if you found it by yourself.

Quote:
can you explain.What can be used instead.

http://www.orafaq.com/forum/m/354126/102589/#msg_354126

Regards
Michel

[Updated on: Fri, 17 October 2008 04:15]

Report message to a moderator

Previous Topic: Maximum Size of LONG datatype in PL/SQL
Next Topic: update multiple rows
Goto Forum:
  


Current Time: Mon Dec 05 19:00:30 CST 2016

Total time taken to generate the page: 0.11279 seconds