mysql select that returns most recent entry from each person in table

Question

I'm trying to come up with a single mySQL query that will take the data below and output the sample output I provided.

Basically what I'm looking for is an output that show only the most recent entry for each person in the table, (their name and the total_points).

The catch here is I only want to show people who have at least 200 points in their most recent entry. In my example output only jeff and bob would have at least 200 points , but ted would NOT and should not be part on the output.

Is there a way to do this in a single query or would I have to break it out into separate queries?

Table Structure:

person------------date--------------------------------------total_points
jeff-----------------2018-07-16 09:00:00----------------300
bob----------------2018-07-15 09:00:00----------------500
ted-----------------2018-07-09 09:00:00----------------100
jeff-----------------2018-07-09 09:00:00----------------700
bob----------------2018-07-03 09:00:00----------------180
ted-----------------2018-06-10 09:00:00----------------1200

Output:

person--------total_points
jeff-------------300
bob------------500

score 1 · Accepted Answer · answered Jul 16 '18 at 15:22

1

One method uses a correlated subquery to get the most recent value:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.person = t.person) and
      t.total_points >= 200;

answered Jul 16 '18 at 15:22

Gordon Linoff

1,242,037
58
646
786

I'm not quite sure I understand your query. is where are t and t2 coming from? – Austin Jul 17 '18 at 16:31
@Austin . . . `t` is your unnamed table in the question. `t2` is a table alias, a fundamental part of SQL. – Gordon Linoff Jul 18 '18 at 02:32

mysql select that returns most recent entry from each person in table

1 Answers1