Order by t

Databases
1 
[b]data in myTable[/b]

[COLOR="Blue"](id) mom geo[/COLOR]

(0) 0 earth
(1) 0 North America
[COLOR="Red"](2)[/COLOR] 1 America
(3) 0 Europe
(4) 2 L.A.
(5) 2 New York
(6) 5 Manhatan
(7) 3 France
(8) 7 Paris

I have data in myTable like the above.

When variables.V is COLOR=“Red”[/COLOR], The SQL below produces the result below.

[b]sql[/b]
select DISTINCT t1.id, t1.mom, t1.geo
  from myTable t1   
 
  join myTable t2
  on t1.id = t2.mom 
 
  join myTable t3
  on t2.id = t3.mom 
  where  t1.id = #variables.V# or
            t2.id = #variables.V# or
            t3.id = #variables.V# 
        order by id
</cfquery>

[b]result[/b]

(0) 0 earth
(1) 0 North America
(2) 1 America

So far, so good.
But let’s suppose (ID) order is not ordered like the below.

[b]data in myTable1[/b]

[COLOR="Blue"](id) mom geo[/COLOR]

(0) 0 earth
([B]2[/B]) 0 North America
[COLOR="Red"](1)[/COLOR] [B]2[/B] America
(3) 0 Europe
(4) [B]1[/B] L.A.
(5) [B]1[/B] New York
(6) 5 Manhatan
(7) 3 France
(8) 7 Paris

When variables.V is 1 with the data in myTable1,
The code below produces the result below.

[b]sql[/b]
select DISTINCT t1.id, t1.mom, t1.geo
  from myTable1 t1   
 
  join myTable1 t2
  on t1.id = t2.mom 
 
  join myTable1 t3
  on t2.id = t3.mom 
  where  t1.id = #variables.V# or
            t2.id = #variables.V# or
            t3.id = #variables.V# 
        order by [COLOR="Red"][B]id[/B][/COLOR]
</cfquery>

[b]result[/b]

(0) 0 earth
(2) 1 America
(1) 0 [COLOR="red"]North America[/COLOR]

How can I get my target result below with the data in myTable1?

[b]target result[/b]

(0) 0 earth
(1) 0 [COLOR="Red"]North America[/COLOR]
(2) 1 America

“order by t” was one of my trials, but it’s failed.
If I remove ORDER CLAUSE, the order seems arbitrary.

2

sorry, no, that is ~not~ the result

the result is

id  mom  geo
 0   0   earth
 2   0   North America

once again, you present a problem but your data is deficient

:slight_smile:

3 
select DISTINCT t1.id, t1.mom, t1.geo
  from myTable1 t1   
 
  join myTable1 t2
  on t1.id = t2.mom 
 
  join myTable1 t3
  on t2.id = t3.mom 
  where  t1.id = #variables.V# or
            t2.id = #variables.V# or
            t3.id = #variables.V# 
        order by id

With the sql above when variables.V is COLOR=“Red”[/COLOR], I am afraid as I check twice or more, the result is

(0) 0 earth
(1) 0 North America
(2) 1 America

Do you mean the data is in myTable is not deficient, but the data in myTable1 is deficient?

4

okay, when i ran my first test on your data, i was using variable.v = 2

i just tried it again, this time with variable.v = 1

id  mom  geo
 0   0   earth
 1   2   America
 2   0   North America

as far as i can tell, the ORDER BY is working correctly

however, the results are ~not~ the same as yours

once again i give up on your problem, i’m far too confused

good luck

5

I guessed.

Yes, your result is right.
I made a mistake, my result above was actually my target result.

I am very sorry for making you confused.

Don’t go, please.
Although you go now, I am expecting you come back later.

6

yes, i plan to be at sitepoint for quite some time

however, for the third or fourth time, i have decided i’m not helping you any more

7

I am afraid of the pain caused by love or thought which is one-sided.

Whether you decide helping me or not, it’s up to your mind.

Waiting is still my job.

8

Wow, and do they pay you for that? :wink:

data in myTable1

(id) mom geo

(0) 0 earth
[B][COLOR="Red"](2) 0 North America
(1) 2 America[/COLOR][/B]

target result

(0) 0 earth
[B][COLOR="red"](1) 0 North America
(2) 1 America[/COLOR][/B]

I don’t understand. Look at the table data and target data you provided. You want to change the value of id and mom for North America and America? How? What is the rule you want to implement?

9

No, I don’t want to change the value of id and mom for North America and America.
I just like to make the order correctly in the order of “earth - North America - America,” although the ID of North America is made later than America.

The target result above is not the target result what I want.
The target result below is what I want.

[b]target result[/b]

(0) 0 earth
(2) 0 [COLOR="Red"]North America[/COLOR]
(1) 2 America

I like to put “(2) North America” between “(0) earth” and “(1) America” although the ID(2) order of it is the last compared with the ID(0) of earth and the ID(1) of America.

I am sorry, I made some mistake again in writing my target result in the first.
I think that’s the reason why rudy was confused.

Yes, they usually pay for it. For the most frquently, rudy do.

10

I like to put “(2) North America” between “(0) earth” and “(1) America” although the ID(2) order of it is the last compared with the ID(0) of earth and the ID(1) of America.

Why? What is the rule you want to apply so it will always work? What is the logic?

11

Let’s suppose like the following.
God created the world (earth:id=0) from nothing(mom=0).
I record the event into my database like the following.

(0) 0 earth

People found North America(id=1) in the earth(mom=0).
I record the event into my database like the following.

(1) 0 North America

People make America(id=2) in the area of North America(mom=1).
I record the event into my database like the following.

(2) 1 America

Now I have data like the following.

(0) 0 earth
(1) 0 North America
(2) 1 America

When variables.V is 2, the sql below produces the result below.

select DISTINCT t1.id, t1.mom, t1.geo
  from myTable t1   
 
  join myTable t2
  on t1.id = t2.mom 
 
  join myTable t3
  on t2.id = t3.mom 
  where  t1.id = #variables.V# or
            t2.id = #variables.V# or
            t3.id = #variables.V# 
        order by id

[b]result[/b]
(0) 0 earth
(1) 0 North America
(2) 1 America

So far so good.

The following is another version(myTable1) of GEO history.

God created the world (earth:id=0) from nothing(mom=0).
I record the event into my database like the following.

(0) 0 earth

People make America(id=1) in the area of some part of North of the earth(mom=2).
I record the event into my database like the following.

(1) 2 America

People calls the area of near America including America “North America(id=2)”.
I record the event into my database like the following.

(2) 0 North America

Now I have data like the following.

(0) 0 earth
(1) 1 America
(2) 0 North America

When variables.V is 2, the sql below produces the result below.

select DISTINCT t1.id, t1.mom, t1.geo
  from myTable1 t1   
 
  join myTable1 t2
  on t1.id = t2.mom 
 
  join myTable1 t3
  on t2.id = t3.mom 
  where  t1.id = #variables.V# or
            t2.id = #variables.V# or
            t3.id = #variables.V# 
        order by id

[b]result[/b]
(0) 0 earth
(1) 1 America
(2) 0 North America

I like to make my target result below with variables.V.

[b]target result[/b]
(0) 0 earth
(2) 0 North America
(1) 1 America
12

Now I have data like the following.

Code:
(0) 0 earth
(1) 1 America
(2) 0 North America

No you do not.
You have

(0) 0 earth
(1) 2 America
(2) 0 North America

Anyway, try

ORDER BY mom, id
13

Yes, your are right. It was,Sorry, my careless.

Yes, that will work fine with data in myTable1.

What about with data in myTable2.

[b]data in myTable2[/b]

(0) 0 earth
(1) 2 New York
(2) 3 America
(3) 0 North America

ORDER BY MOM will produce the result2 below.

[b]result2[/b]

(0) 0 earth
(3) 0 North America
(1) 2 New York
(2) 3 America

My target result2 is like the below.

[b]target result2[/b]

(0) 0 earth
(3) 0 North America
(2) 3 America
(1) 2 New York

In order to get my target result2, I guess, ORDER should relate BY t something like the following.

ORDER BY [COLOR="red"]T[/COLOR]1.mom, [COLOR="red"]T[/COLOR]2.mom, [COLOR="red"]T[/COLOR]3.mom