PLSQL Challenge 1 - Pair-wise and ordered assignment of objects from two different lists - Solution 16 By Niladri Biswas

Getting Started with Adobe After Effects - Part 6: Motion Blur


	ORACLE - PLSQL Challenges

Upload Image

Close it

Select File

This challenge involves assigning food packets to birds from two different baskets. Your job is to read the input table and produce the required result set using a single PLSQL query.


	Like

Invite Friends

PLSQL Challenge 1 - Pair-wise and ordered assignment of objects from two different lists

Home · Challenge Details · Comments · Solutions · Fans (13) · Preferences

Solution 16 By Niladri Biswas

Oct 21 2011 9:03AM by Niladri Biswas

SELECT 	
	b.Code
	,b.Name AS Bird
	,g.Grain
	,f.Fruit
FROM PLC1_BIRDS b
LEFT JOIN
(	
	(SELECT 
		DENSE_RANK() OVER(PARTITION BY Code ORDER BY Fruit) RowNumber
		,Code
		,Fruit
	FROM PLC1_FRUITS) f
	FULL JOIN 
	(SELECT 
		DENSE_RANK() OVER(PARTITION BY Code ORDER BY Grain) RowNumber
		,Code
		,Grain
	FROM PLC1_GRAINS) g
	ON f.Code = g.Code AND f.RowNumber = g.RowNumber
)
ON b.CODE = g.Code 
OR  b.CODE = f.Code;

Tags: DENSE_RANK(), FULL JOIN

Niladri Biswas

7 · 21% · 6710

Liked


Post this to my facebook wall
Tweet this
Like this


	Submit

Your Comment

Sign Up or Login to post a comment.

PLSQL Challenge 1 - Pair-wise and ordered assignment of objects from two different lists

Solution 16 By Niladri Biswas

Your Comment