Dejell Dejell - 9 months ago 29
SQL Question

Efficient SQL to retrieve top results

I have the following table:

id, first_name, last_name, score, feedback, report_date
1, Barry , Smith , 5 , positive, 2017-02-01
2, John , Smith , 6 , negative, 2017-02-01
3, Barry , Smith , 3 , negative, 2017-01-31
5, John , Smith , 1 , positive, 2017-01-31


I would like to retrieve the latest score and feedback for first and last name.

I tried the following query:

select m.ad_id, m.country, m.score, m.feedback
from records m
inner join (
select first_name, last_name, max(report_date) as max_date
from records
where report_date <= '2017-02-19 15:00:00'
and report_date >='2017-02-19 10:00:00'
and score is not null
GROUP BY first_name, last_name
) mp on m.first_name = mp.first_name
and m.last_name=mp.last_name
and m.report_date = mp.max_date
and m.report_date <= '2017-02-19 15:00:00'
and m.report_date>='2017-02-19 10:00:00'
;


However, on 18M records it takes a few seconds.

Making a simple query:

select m.fist_name, m.last_name, m.score, m.feedback
from records m
where m.report_date <= '2017-02-19 15:00:00'
and m.report_date>='2017-02-19 10:00:00'
and score is not null


Taking less than half a second, however it would return x amount of records which takes more traffic.
How can I make the first query more efficient?

Answer Source

distinct on

select distinct on (first_name, last_name) *
from records
where
    report_date <= '2017-02-19 15:00:00' and report_date >='2017-02-19 10:00:00' 
    and score is not null
order by first_name, last_name, report_date desc