getting ids from group of ids

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

getting ids from group of ids

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Can you clarify this for us. I am a little lost as to what you need.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

Post 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
Last edited by thatsme on Sat Dec 15, 2007 2:18 am, edited 1 time in total.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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?
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

Post by thatsme »

Database is designed and populated by some other person. I have to import it and use it.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You can import it and convert it to a proper design. Programmatically or otherwise.
thatsme
Forum Commoner
Posts: 87
Joined: Sat Apr 07, 2007 2:18 am

Post 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 :(
Post Reply