select max(path) on number order ...

Thread View: borland.public.jdatastore

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Magnus Tue, 13 Dec 2005 19:57

#72

Author: Magnus
Date: Tue, 13 Dec 2005 19:57

18 lines
1533 bytes

Hi,

I have a tree struckture in a table and to keep data in order I add a 
path with every ID.

The column is defined as: PATH VARCHAR(50) NOT NULL

When I later do SELECT MAX(PATH) I get 0.9 before 0.10 as a string 
should. For some reason did I never reflect upon this before and it 
seems like no one tested with more than a 10 wide path before either.

The path can grow to 50 char long so this must be able to find the end 
number and sort out the max from that (i.e 0.1.1.10 before 0.1.1.9)

Is there a way to handel this in SQL or do I need to select all and loop 
in java?

TIA

Re: select max(path) on number order ...

#74

Author: Lori M Olson [TeamB]
Date: Wed, 14 Dec 2005 16:55

43 lines
2424 bytes

Magnus wrote:
> Hi,
> 
> I have a tree struckture in a table and to keep data in order I add a 
> path with every ID.
> 
> The column is defined as: PATH VARCHAR(50) NOT NULL
> 
> When I later do SELECT MAX(PATH) I get 0.9 before 0.10 as a string 
> should. For some reason did I never reflect upon this before and it 
> seems like no one tested with more than a 10 wide path before either.
> 
> The path can grow to 50 char long so this must be able to find the end 
> number and sort out the max from that (i.e 0.1.1.10 before 0.1.1.9)
> 
> Is there a way to handel this in SQL or do I need to select all and loop 
> in java?
> 
> TIA

I don't think there is a way to do this in standard SQL.  I'm pretty 
sure I could cobble something together with the new regular expression 
stuff in Oracle 10g, but that's not standard SQL, so I doubt you can do 
it in JDataStore.

-- 

Regards,

Lori Olson [TeamB]

------------

Save yourself, and everyone else, some time and search the
newsgroups and the FAQ-O-Matic before posting your next
question.

Google Advanced Newsgroup Search
     http://www.google.ca/advanced_group_search
Other Newsgroup Searches:
     http://www.borland.com/newsgroups/ngsearch.html
Joi Ellis's FAQ-O-Matic:
     http://www.visi.com/~gyles19/fom-serve/cache/1.html

Re: select max(path) on number order ...

#75

Author: Magnus
Date: Fri, 16 Dec 2005 14:33

21 lines
1821 bytes

Lori M Olson [TeamB] wrote:
>>
>> Is there a way to handel this in SQL or do I need to select all and 
>> loop in java?
>>
>> TIA
> 
> 
> I don't think there is a way to do this in standard SQL.  I'm pretty 
> sure I could cobble something together with the new regular expression 
> stuff in Oracle 10g, but that's not standard SQL, so I doubt you can do 
> it in JDataStore.
> 
Thanks Lori,

After some searching and thinking I came to the same conclusion and do 
the logic in lava.
Another problem (addressed earlier in this group) is when I move a 
sub-tree within my structure. As I can't (in JDataStore) insert a 
location and then find that with a select (before committing it all) so 
there is a lot of looping through my arrays.

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads