SQL查找每个类别的最大值

Question

I'm trying to do some practice problems on sql questions because I realized that I'm struggle on them.

I've been playing around with a sample database of mine and I was trying to list out names of restaurants, date inspected and their total score, BUT only the most recent date inspected for each restaurant would be shown.

restaurant has a rid that is a foreign key to inspection and a name. inspection has an rid, idate (date inspected) and the totalscore from an inspection.

How would I get a list of restaurants with their most recent inspection scores and the dates of those inspections?

SELECT i.idate, r.name, i.totalscore 
FROM restaurant r 
JOIN inspection i 
ON r.rid = i.rid where
 i.idate = (SELECT i1.idate
                  FROM restaurant r1
                  JOIN inspection i1
                  ON r1.rid = i1.rid
          order by idate desc limit 1
                  );

Thank you!

Answer 1

Much easier then that

SELECT
    i.idate, r.name, i.totalscore 
FROM restaurant r 
JOIN inspection i 
ON r.rid = i.rid
where 1
GROUP BY r.name ORDER BY i.idate desc