>Hi there,
>I have this problem that I think is simple.
>I want to set up a table where I store messageboard posts (kinda like UT).
>I am thinking that one PK field and one field stating which record it is related to. If it is a new thread it is related to itself. And the fields for other stuff like subject and body and stuff.
>Considder this:
>
>sele 0
>crea curs mycursor (npk i,nrk i, cit c(5))
>insert into mycursor (npk,nrk,cit) valu (1,1,"A")
>insert into mycursor (npk,nrk,cit) valu (2,1,"AA")
>insert into mycursor (npk,nrk,cit) valu (3,2,"AAA")
>insert into mycursor (npk,nrk,cit) valu (4,4,"a")
>insert into mycursor (npk,nrk,cit) valu (5,4,"aa")
>insert into mycursor (npk,nrk,cit) valu (6,6,"einar")
>insert into mycursor (npk,nrk,cit) valu (7,3,"AAAA")
>insert into mycursor (npk,nrk,cit) valu (8,7,"AAAAA")
>insert into mycursor (npk,nrk,cit) valu (9,5,"aaa")
>
>
>then i want to do a select statement. Something like:
>
>select * from mycursor orde by nrk,npk
>
>the problem is that it doesn't order the querry the way i want it.
>I want the result to be:
>
>1,1,A
>2,1,AA
>3,2,AAA
>7,3,AAAA
>8,7,AAAAA
>4,4,a
>5,4,aa
>9,5,aaa
>6,6,einar
>
>
>Can this be done in one select statement? ( can do it myself by scaning the cursor recurively, but i've got a feeling it can be done with a select statement)
>
>Any help would be great.
>
>Thanks,
>Einar
Einar,
I don't think it at least could be done with VFP's SQL. I use this skeleton for sorting NodeID,ParentID lists :
Select *, npk*0 As Level, npk As RootId ;
from mycursor ;
into Cursor crsTemp ;
readwrite
m.level = 0
Do While .T.
Select npk,RootId From crsTemp ;
where Level=m.level And (m.level#0 Or npk=nrk) ;
into Array arrLevel
If (_Tally>0)
For ix=1 To Alen(arrLevel,1)
Replace Level With m.level+1, ;
RootId With arrLevel[m.ix,2] ;
for npk # nrk And nrk = arrLevel[m.ix,1]
Endfor
Else
Exit
Endif
m.level = m.level + 1
Enddo
Select npk,nrk,cit From crsTemp Order By RootId,Level,npkCetin
