OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Need query (Oracle Database 11g Enterprise Edition Release )

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

Need query [message #625946]

Thu, 16 October 2014 10:56

Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune

Member

Hi All,

I have two tables. One is master and another is details table. I want to select data from Detail table which is depend on Master table.

Suppose

Master table data
Name No
A 2
B 3

Details table data
Name No
A 10
A 12
A 45
A 78
A 856
B 412
B 74
B 652
B 4523

In Master table for the column "Name" has value A and for the column "NO" has value 2 and B has value 3. Now i want to fetch only 2 Values for A and 3 Values for B from Details table. With this mail i am attaching table scripts and sample data.

Thanks.

Attachment: Sample.txt
(Size: 3.21KB, Downloaded 1256 times)

[Updated on: Thu, 16 October 2014 11:02]

Report message to a moderator

Re: Need query [message #625947 is a reply to message #625946]

Thu, 16 October 2014 11:19

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

Senior Member
Account Moderator

Quote:

Now i want to fetch only 2 Values for A and 3 Values for B from Details table.

Which ones?

Report message to a moderator

Re: Need query [message #625950 is a reply to message #625947]

Thu, 16 October 2014 13:01

Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune

Member

Michel,

I need data like below, i need data from only Details tables, first 2 values for A and First 3 values for B from Details table( As in Master table for name A has value 2 and for name B has value 3)

Name No
A 10
A 12
B 412
B 74
B 652

Report message to a moderator

Re: Need query [message #625951 is a reply to message #625950]

Thu, 16 October 2014 13:04

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

Senior Member
Account Moderator

Fist does not exist until you define an order.
Which ones are the first 2 apples in a basket?

Report message to a moderator

Re: Need query [message #625952 is a reply to message #625951]

Thu, 16 October 2014 13:11

Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune

Member

Ok...maximum value in No column... Result will be some thing like that

Name No
A 78
A 45
B 4523
A 856
B 652

Report message to a moderator

Re: Need query [message #625954 is a reply to message #625952]

Thu, 16 October 2014 13:16

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

Senior Member
Account Moderator

with 
  data as (
    select name, no, row_number() over (partition by name order by no desc) tn
    from details
  )
select d.name, d.no
from master m, data d
where d.name = m.name
  and d.rn <= m.no
/

Of course, you have to define how you manage even NO.

Report message to a moderator