Re: difficult select- question

From: Andreas Mosmann <mosmann_at_expires-30-11-2009.news-group.org>
Date: Tue, 17 Nov 2009 17:48:49 +0100
Message-ID: <1258476529.13_at_user.newsoffice.de>

Maxim Demenko schrieb am 17.11.2009 in <4B024E96.2010503_at_gmail.com>:

> Andreas Mosmann wrote:

>> Andreas Mosmann schrieb am 02.11.2009 in
>> <1257181408.57_at_user.newsoffice.de>:
>>

>>> Maxim Demenko schrieb am 27.10.2009 in <4AE6A3AF.4020506_at_gmail.com>:

>>
>> Similar question, similar answer?
>>
>> the situation before is
>>
>> F1|F2|F3
>> AA|BB|10
>> AA|BB|20
>> AA|BB|40
>> AA|BB|50
>> AA|BB|60
>> AA|BB|70
>> AA|CC|70
>> AA|CC|80
>> AA|CC|90
>>
>> and the result should be
>>
>> F1|F2|F3|F4
>> AA|BB|10|20
>> AA|BB|20|30
>> AA|BB|40|50
>> AA|BB|50|60
>> AA|BB|60|70
>> AA|CC|70|80
>> AA|CC|80|90
>>
>> Is there a similar way to get this? Your way is very fast.
>>
>> Many thanks in advance
>> Andreas Mosmann
>>

> Could you explain a bit the logic behind your desired results? > In particular, it's unclear for me, based on what should the line No problem. We talk about streets, that are handled as a net of nodes and edges. The position of a point object is described as a combination of both nodes and the relative position to the first node. If I f.e. have an Edge AA->BB with the length 100 and there are Points by 10, 30 and 40 I at the end need something like that

N1|N2|POS
AA|BB|10
AA|BB|30
AA|BB|40

should become

N1|N2|FROM|TO
AA|BB| 10 |30
AA|BB| 30 |40

I also have to add
AA|BB| 0 | 10
AA|BB| 40 |100
but I am afraid not understanding the answer if I you fill out these lines too.
In the table I of course have more than one pair of nodes. If I would do this by algorithm it would be easy. It is easy to find a query like
SELECT
  N1,
  N2,
  POS as FROM,
  (SELECT MIN(POS) FROM STREETS I WHERE I.N1=O.N1 and I.N2=O.N2 and I.POS>O.POS) as TO
FROM
  STREETS O (I should no use from as a column name, I know) But this query of course is very slow.

Is there a place I can read about handling the type of query you used?

> AA|BB|20|30

> be generated

> Best regards

> Maxim
Many thanks again,
I am really excited

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Tue Nov 17 2009 - 10:48:49 CST