468,550 Members | 1,693 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,550 developers. It's quick & easy.

Select pair of rows that obey a rule

I have a big table (1M rows) with the following columns:
source, dest, distance.
Each row defines a link (from A to B).

I need to find the distances between a pair using anoter node.
An example:
If want to find the distance between A and B,
If I find a node x and have:
x -> A
x -> B
I can add these distances and have the distance beetween A and B.
My question:
How can I find all the nodes (such as x) and get their distances to (A and B)?
My purpose is to select the min value of distance.

P.s: A and B are just one connection (I need to do it for 100K connections).
Thanks !
Oct 24 '11 #1
5 2379
12,513 Expert Mod 8TB
You must mean from A to x and then from x to B. Otherwise, it doesn't make much sense. If we are talking at most one intermediate node, you can just join the table to itself, filtering for you start and end node, and doing an aggregate query. However, if you are talking about n-number of nodes where n is not known, your table structure does not support that type of query. You would have to redesign your table to include a tree data structure of some sort.
Oct 24 '11 #2
Thanks Rabbit.
There is no mistake with the directions:
For measuring A->B, I'm looking for the node (X) that gives a minimum value for dist(X->A) + dist(X->B).
I have several topologies I need to check (2 nodes...) but I first want to solve the basic one.
Can you help me with the query your suggested (using JOIN)?
Oct 27 '11 #3
12,513 Expert Mod 8TB
My point is that solving for the specific form is a waste of time if your end goal is to solve for the generic form. Yes, we can get a solution to solve for the 1-intermediary node problem, but the solution for n-intermediary node is completely different.
Oct 27 '11 #4
I'm not interested in a solution for n-intermediary node, but only in several topologies (for 1 and 2 intermediary nodes).
I want to try with a simple topology and derive the others respectively.
I'll be happy to get help with the query.
Oct 28 '11 #5
12,513 Expert Mod 8TB
Basically, join the table to itself and filter for the end points.
Expand|Select|Wrap|Line Numbers
  1. SELECT s.NodeB AS starting,
  2.    e.NodeB AS ending,
  3.    s.Distance + e.Distance AS totalDistance
  4. FROM tableName AS s
  5. INNER JOIN tableName AS e
  6. ON s.NodeA = e.NodeA
  7.    AND s.NodeB != e.NodeB
  8. WHERE s.NodeB = 'A' AND e.NodeB = 'B'
Oct 28 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Raj Chudasama | last post: by
2 posts views Thread by Kevin Hodgson | last post: by
4 posts views Thread by sanjeev.atvankar | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by UniDue | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.