Home » SQL & PL/SQL » SQL & PL/SQL » Assistance with retrieving rows limited by the value of specific fields (Oracle 10.2.0.4.0 (WinXP, sp2))
Assistance with retrieving rows limited by the value of specific fields [message #352768] Thu, 09 October 2008 08:20 Go to next message
drac
Messages: 7
Registered: June 2004
Location: Denmark
Junior Member
Hello everybody,
I have a question concerning limiting the result set of my query.

I have a table Node:

CREATE TABLE NODE
(
NODEID NUMBER(19) NOT NULL,
TREELEVEL NUMBER(10) NOT NULL,
MODULEID NVARCHAR2(264)
)

and a table Module:

CREATE TABLE MODULE
(
MODULEID NVARCHAR2(264) NOT NULL,
SERIAL NVARCHAR2(256) NOT NULL
}

A module can be attached to several nodes in the system with the same or different treelevels

What I've been trying to select is a row for each module in the system with the attached node.nodeid and node.treelevel:
select m.moduleid, n.nodeid, n.treelevel 
from module m join node n on(n.moduleid=m.moduleid))

But I want to limit the search to contain only one row for each module no matter the number of nodes it is connected to. And I want the one with the lowest treelevel. So if the same module is connected to several nodes..

e.g.
a) module m1 with moduleid=1 on node n1 with treelevel=3
b) module m1 with moduleid=1 on node n2 with treelevel=4
c) module m1 with moduleid=1 on node n3 with treelevel=3

..I only want either row a) or c). Either will do, it's the lowest treelevel that dictates.

I think I've found something (just an idea) on the way to do the 'lowest treelevel' bit:
select moduleid, treelevel from node n1 
where rowid > (select min(rowid) 
from node n2 where n1.moduleid = n2.moduleid)

But I need to connect it with the the uniqueness. I thought I could use DISTINCT but I can't as I found out it applies to the entire select list and not just the first entry as I thought (I'm mostly only doing rather simple queries)


I'm mostly a 'select + join + order by' sql user who has had at best sporadic experience with the rest. So if there is anyone out there who has a neat way of doing this or perhaps some pin pointed/specific documentation (that doesn't span 100+ pages Smile), I'd be most grateful. Any help or hints are highly appreciated.


-Maya

[Updated on: Thu, 09 October 2008 08:36] by Moderator

Report message to a moderator

Re: Assistance with retrieving rows limited by the value of specific fields [message #352773 is a reply to message #352768] Thu, 09 October 2008 09:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
must you have an actual rowid and row? or can you just take the min(treelevel) by module and be done with it?

Kevin
Re: Assistance with retrieving rows limited by the value of specific fields [message #352777 is a reply to message #352773] Thu, 09 October 2008 09:23 Go to previous messageGo to next message
drac
Messages: 7
Registered: June 2004
Location: Denmark
Junior Member
Kevin Meade wrote on Thu, 09 October 2008 16:01
must you have an actual rowid and row? or can you just take the min(treelevel) by module and be done with it?


The row thing was just an attempt I found somewhere which partially did what I wanted, nothing written in stone.
As to the treelevel by module, well that was what I wanted; for each module in the system, find all the nodes it's connected to and get moduleid, nodeid and treelevel for the one with the lowest treelevel value.

I just lack the deduction skills to get there Smile
Re: Assistance with retrieving rows limited by the value of specific fields [message #352783 is a reply to message #352777] Thu, 09 October 2008 10:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Then why not use min(treelevel) and be done with it.
Re: Assistance with retrieving rows limited by the value of specific fields [message #352875 is a reply to message #352783] Fri, 10 October 2008 00:17 Go to previous messageGo to next message
drac
Messages: 7
Registered: June 2004
Location: Denmark
Junior Member
Because if I use min(treelevel) and have a module on two nodes with equal treelevel, I'll have both returned and I only want one of them. Remember, treelevel is on node, not module.

select m.moduleid, m.serial, n.nodeid, n.treelevel 
from module m  
join node n on((m.moduleid = n.moduleid)
and (treelevel = (select min(treelevel) from node where moduleid=m.moduleid)))                    
order by n.moduleid asc, n.treelevel asc, n.nodeid asc

[Updated on: Fri, 10 October 2008 00:39]

Report message to a moderator

Re: Assistance with retrieving rows limited by the value of specific fields [message #352890 is a reply to message #352875] Fri, 10 October 2008 01:51 Go to previous messageGo to next message
drac
Messages: 7
Registered: June 2004
Location: Denmark
Junior Member
After my fourth day of SQLing this query and getting help from various forums, this is what I've come up with:

select n.moduleid, n.nodeid, m.serial, n.treelevel 
from module m  
join node n on(m.moduleid = n.moduleid)
MINUS
select n.moduleid, n.nodeid, m.serial, n.treelevel 
from module m  
join node n on(m.moduleid = n.moduleid)
where n.ROWID <> (select min(rowid) from node n2 where n.moduleid = n2.moduleid)          


But I've always been told to avoid UNION, MINUS and INTERSECT as they were regarded as heavy usage, should this bother me?

Re: Assistance with retrieving rows limited by the value of specific fields [message #352911 is a reply to message #352890] Fri, 10 October 2008 03:53 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Search for Row_Number analytic function and see if that is of any help to you.

Regards

Raj
Re: Assistance with retrieving rows limited by the value of specific fields [message #352913 is a reply to message #352911] Fri, 10 October 2008 04:07 Go to previous message
drac
Messages: 7
Registered: June 2004
Location: Denmark
Junior Member
Thanks, I'll do that Smile

-Maya
Previous Topic: Commit after DDL
Next Topic: algorithms graphs
Goto Forum:
  


Current Time: Fri Dec 09 19:39:06 CST 2016

Total time taken to generate the page: 0.09336 seconds