Как выбрать последние записи?

Question

[enchikiben]

Добрый день!
Есть таблица (group):
id name
1 aaa
2 bbb
3 cccc

и есть таблица (stat) статистики для этих записей
id group_id date value
1 1 2015-12-12 1
2 1 2015-12-15 5
3 2 2015-12-11 3
4 3 2015-12-12 2
5 1 2015-12-14 1
6 2 2015-12-18 3

нужно одним запросом выбрать статистику на последнюю дату должно получиться что то типа:
name date value
aaa 2015-12-15 5
bbb 2015-12-18 3
ccc 2015-12-12 2

Возможно такое?

Comments

[Вячеслав]

возможно

Answer 1

[Петр]

select g.name, st.date, st.value from group g
inner join (select group_id, max(date) md from stat group by group_id) s on s.group_id = g.id
inner join stat st on st.group_id = g.id and st.date = s.md

Answer 2

[Константин Б.]

SELECT * FROM stat, group WHERE group.id = stat.group_id ORDER BY date DESC LIMIT 5

Answer 3

[nozzy]

select 
t1.name,
t2.d,
t2.value
from group t1
inner join
(
  select
  group_id,
  value,
  max(date) as d
  from stat
  group by group_id,  value
  ) t2 on t2.group_id = t1.id

Answer 4

[Rsa97]

SELECT `g`.`name`, `s`.`date`, `s`.`value`
    FROM (
        SELECT `group_id`, MAX(`date`) AS `date`
        FROM `stat`
        GROUP BY `group_id`
    ) AS `d`
    JOIN `stat` AS `s` ON `s`.`date` = `d`.`date` AND `s`.`group_id` = `d`.`group_id`
    JOIN `group` AS `g` ON `g`.`id` = `d`.`group_id`