CODEWITHSUNDEEP
mysqlsqlperformance
XSigno
XSigno

Reputation: 11

Select max on 2 following columns mysql

I have a table like

date(int), time(int), code(int), title(varchar)

I should select the last row for a specific code, then the row with max time in the max date. Table is VERY big, and I should use less resource as possbile in the query

table

date     time code title
20161110 1045 5522 plant1
20161110 1045 5522 plant1
20161110 1100 5522 plant1
20161111 1030 5522 plant1
20161111 1045 5522 plant1

I expect to obtain one row

20161111 1045 5522 plant1

and that's my crappy sql

select * 
from ep_ft_consumptions_experimental as cons 
    (inner join (select max(date) as md 
                 from ep_ft_consumptions_experimental 
                 where plant_code=5522 
                   and d1=1
                ) dr 
        on cons.date = dr.md) 
    (inner join (select max(time) as mt 
                 from ep_ft_consumptions_experimental
                ) tr 
        on cons.time = tr.time

Upvotes: 0

Views: 74

Answers (1)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

It's very simple. Use ORDER BY and LIMIT 1:

select cons.* 
from ep_ft_consumptions_experimental as cons 
where cons.code = 5522
order by cons.date desc, cons.time desc
limit 1 ;

As for efficiency, you only need to add a composite index on (code, date, time) if you haven't one already.

Upvotes: 1

Related Questions