How make this query?

[eluser]huesoamz[/eluser]
Hi, I try to learn CI. Today I make a query but this is dificult.


SELECT id FROM blog where (categoria='animal') AND (peso=(SELECT MAX(peso) FROM blog where (categoria='animal')))


For now the only form avaliable is separate and make two functions in model, one for search the max peso and later with "this peso" search the ID, i think so that the best way is make 1 query and no TWO.


Thanks

[eluser]33cent[/eluser]
SELECT id, peso FROM blog WHERE categoria=animal ORDER BY peso DESC LIMIT 1

[eluser]vitoco[/eluser]
if you wanna get only one id, order the rows by "peso DESC", and get the first id with LIMIT 0,1 like this :

Code:

$sql = "
    SELECT
        id ,
        peso
    FROM
        blog
    WHERE
        categoria = ‘animal’
    ORDER BY
        peso DESC
    LIMIT
        0,1
";

Saludos

[eluser]huesoamz[/eluser]
$this->db->select('id,peso');
$this->db->where('categoria','animal');
$this->db->order_by("peso", "desc");
$this->db->limit(1);
$query = $this->get('blog');

Is correct?, but i think so that if..if more than one animal have the same "peso", i lose animals...because i only take 1..and not ALL MAX PESO.

Tks

[quote author="vitoco" date="1291166678"]if you wanna get only one id, order the rows by "peso DESC", and get the first id with LIMIT 0,1 like this :

Code:

$sql = "
    SELECT
        id ,
        peso
    FROM
        blog
    WHERE
        categoria = ‘animal’
    ORDER BY
        peso DESC
    LIMIT
        0,1
";

Saludos[/quote]

Vitoco gracias, pero cual es la joda de usar Codeigniter si al final usamos php...la idea es usar el framework

[eluser]huesoamz[/eluser]
I find for solution this issue, the dificult is make using CI...

SELECT b.* FROM `blog` B join (SELECT categoria, MAX( peso ) as upt FROM `blog` GROUP BY categoria) C on (b.categoria = c.categoria and b.peso=upt)

Is possible make this query using CI???

EDIT:

Yes is posible but...is pure mysql not "full CI implementation"...

function get_test(){
$this->db->select('b.* FROM blog B');
$this->db->join('(SELECT categoria, MAX( peso ) as upt FROM blog GROUP BY categoria) C', 'b.categoria=c.categoria and b.peso=upt');
$query3= $this->db->get();

if($query3->num_rows>0){
foreach ($query3->result() as $fila) {
$data[] = $fila;
}
return $data;
}

}