Page 1 of 1
getting ids from group of ids
Posted: Fri Dec 14, 2007 9:51 am
by thatsme
I have a table where the ids are in the pattern,
1.a
1.a.2
1.a.3
1.a.4.5
2.a
2.b.a
2.b.a
2.b.c
I want all the rows which has highest in that branch.
In the above example, i need
1.a.4.5
2.b.a
2.b.a
2.b.c
Posted: Fri Dec 14, 2007 11:13 am
by RobertGonzalez
Can you clarify this for us. I am a little lost as to what you need.
Posted: Fri Dec 14, 2007 12:02 pm
by VladSun
Me too... I think you should get this output:
1.a.4.5
2.b.c
because 2.b.a < 2.b.a < 2.b.c
Maybe you have "depth" in mind?
Posted: Fri Dec 14, 2007 8:30 pm
by thatsme
I need the highest branch ids
1.a - 2
1.a.2 - 3
1.a.3 - 3
1.a.4.5 - 4
2.a - 2
2.b.a - 3
2.b.a - 3
2.b.c - 3
In first case, i need, 1.a.4.5 as it is the highest(4) number of characters
in the second case i need, 2.b.a, 2.b.a, 2.b.c as they are having the highest(3) number of characters
Posted: Fri Dec 14, 2007 10:21 pm
by John Cartwright
This will take a fairly complex query to manage grouping the elements together and grabbing the last in the branch. An alternative would be to sort using php although that would be horribly inefficient.
Have you considered improving your database design?
Posted: Fri Dec 14, 2007 11:23 pm
by thatsme
Database is designed and populated by some other person. I have to import it and use it.
Posted: Sat Dec 15, 2007 2:56 am
by feyd
You can import it and convert it to a proper design. Programmatically or otherwise.
Posted: Sat Dec 15, 2007 3:11 am
by thatsme
i am trying to use PHP.
Code: Select all
$getOccupation_RES = mysql_query("SELECT SUBSTRING(element_id, 1,1) AS first_character, `element_id`,`element_name` FROM tbl_element");
while($getOccupation_RET = mysql_fetch_array($getOccupation_RES))
{
$first_character[] = $getOccupation_RET['first_character'];
$element[] = $getOccupation_RET['element_id'];
}
for($i=0; $i<=count($first_character); $i++)
{
$highest = $element_id[$i];
if(strlen($element[$i]) >= $highest)
$highest = $element[$i];
// echo "FCharacter : $first_character[$i] Length". strlen($element[$i]);
// echo '<br/>';
}
echo $highest;
}
I am trying to get all elements and then comparing their stringlength. Got struct here
