Welcome Guest, Not a member yet? Register   Sign In
Select Greatest SQL Help
#1

[eluser]datguru[/eluser]
Hi there, I'm trying to select the greatest ID out of my database but it is not a normal select greatest query.

Here is the SQL:
Code:
$this->db->select("*");
$this->db->from('progress');
$this->db->where('completed', 1);
$this->db->where_in('instituion_id', $inst_id);
$this->db->order_by('steps_id', 'desc');
$this->db->join('institutions', 'institutions.id = progress.institution_id', 'right');
$this->db->limit(sizeof($inst_id));
$data['instname'] = $this->db->get();

So what I'm trying to do is join the institution table since it contains the name of the institution to the progress table since it contains the highest step. This query almost works apart from it selects multiple steps from the institutions and not just one step for each institution. Is there a way to "select ONLY the highest step id for each institution"?

Thanks for your time.
#2

[eluser]GSV Sleeper Service[/eluser]
yep, you can use a mixture of MAX and GROUP BY.
I've got no idea how to do this using the 'active record' db stuff, proper SQL all the way for me
#3

[eluser]datguru[/eluser]
Hey thanks for the reply how would you do that in normal SQL (never used those two SQL functions before so I'm not sure on the logic). Im sure I can translate it into active record after since I know there is a group by command. Thanks again!

Edit: Got it working! Thanks for your help just to let people know all I did was add the following.

Code:
$this->db->select_max('steps_id');
$this->db->group_by('inst_name');

Thanks again for your help Big Grin




Theme © iAndrew 2016 - Forum software by © MyBB