using group by with count

[raj_2006]

Hi all,

I have a tree structure.I am displaying the depth of the tree inside a dropdown.But the depths are repeating.The table contains the following data:

name lft rgt

ELECTRONICS 1 22
TELEVISIONS 2 9
TUBE 3 4
LCD 5 6
PLASMA 7 8
GAME CONSOLES 10 11
PORTABLE ELECTRONICS 12 21
MP3 PLAYERS 13 16
FLASH 14 15
CD PLAYERS 17 18
2 WAY RADIOS 19 20

Code :
$sql="SELECT node.name, (COUNT(parent.name) - 1) AS depth,node.lft,node.rgt FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft";

O/P:

0
1
2
2
2
1
1
2
3
2
2

I just want this output to be

0
1
2
3


Please suggest me how can I get the unique depth.

Thanks in advance for your co-operation.

Raj

[chazzy]

the only thing that unique depth can tell you in this case is the highest depth. the data you're getting makes perfect sense based on your tree structure.

[raj_2006]

Yeah the data which I am getting is totally correct...but how to display the highest depth.