CODEWITHSUNDEEP
mysqlgroup-by
rafffael
rafffael

Reputation: 81

MySQL, group by and latest result

SELECT
o.semaine AS week,
c.id AS c_id,
sr.id AS sr_id,
mr.id AS mr_id,
o.`%_RACH_distance_sup_5075m` AS rach,
o.DCR_Total_AMR AS dcr_amr
FROM
rdi.ref_cells AS c
INNER JOIN nortel.OM_Cell_week AS o ON o.cellule = c.libelle
INNER JOIN rdi.ref_sites AS sr ON sr.id = c.siteRadio_id
INNER JOIN rdi.ref_mres AS mr ON o.rnc = mr.libelle AND sr.milieuReseau_id = mr.id
INNER JOIN rdi.ref_pl AS p ON c.plaque_id = p.id
WHERE
o.date > ADDDATE(NOW(), INTERVAL - 3 WEEK) AND
o.`%_RACH_distance_sup_5075m` > 50 AND
o.DCR_Total_AMR > 1.5

The result:

+------+--------+-------+-------+---------+---------+
| week | c_id   | sr_id | mr_id | rach    | dcr_amr |
+------+--------+-------+-------+---------+---------+
|   16 | 117114 | 37312 |    79 | 64,1198 |  1,5018 |
|   17 | 117114 | 37312 |    79 | 67,6647 | 1,79469 |
|   18 | 117114 | 37312 |    79 | 66,6645 | 1,51302 | <- this
|   16 | 117116 | 37312 |    50 | 69,1325 |  2,3014 |
|   17 | 117116 | 37312 |    50 | 67,6647 |   1,568 | <- this
+------+--------+-------+-------+---------+---------+

I'd like to select the latest results (line 3 and 5) where the week is the highest. I tried to add GROUP BY c.id but by default it returns the first of each group. I also tried ORDER BY o.semaine

Upvotes: 1

Views: 376

Answers (2)

Joel
Joel

Reputation: 3454

The two lines you pointed are not the ones where the week is the highest, so i'm not sure what you mean.

Anyway, you can always use sub-queries to perform such things. Here is a sample for selecting the rows with the highest week:

select * from table where week = (select max(week) from table)

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425251

SELECT  o.semaine, c.id, sr.id, mr.id
FROM    rdi.ref_cells c
JOIN    nortel.OM_Cell_week o
ON      o.id = 
        (
        SELECT  o.id
        FROM    nortel.OM_Cell_week oi
        WHERE   oi.cellule = c.libelle
                AND oi.date > ADDDATE(NOW(), INTERVAL - 3 WEEK)
                AND oi.`%_RACH_distance_sup_5075m` > 50
                AND oi.DCR_Total_AMR > 1.5
        ORDER BY
                oi.week DESC, oi.id DESC
        LIMIT 1
        )
JOIN    rdi.ref_sites AS sr
ON      sr.id = c.siteRadio_id
JOIN    rdi.ref_mres AS mr
ON      mr.libelle = o.rnc
        AND mr.id = sr.milieuReseau_id
JOIN    rdi.ref_pl AS p
ON      p.id = c.plaque_id

Upvotes: 1

Related Questions