MYSQL 从重复条目中获取最高值

Question

我未能构建复杂的查询。请参阅我的查询下的结果示例。我希望我的查询仅返回具有最高日期 (2014-02-19 16:25:11) 的条目，其中program_id、名称和类型相同，但值不同。

SELECT
    LPAD(convert( master_program.program_id USING utf8),
            5,
            '00000') AS program_id,
    rate.name,
    rate.type,
    rate.value
FROM
    master,
    master_program,
    programs,
    rate_group,
    rate
WHERE
    master.id = master_program.master_id
        AND master_program.program_id = programs.id
        AND rate_group.master_id = master.id
        AND rate_group.id = rate.rate_group_id
        AND master.is_deleted = 0
        AND rate_group.is_deleted = 0
        AND master.is_payout = 0
ORDER BY program_id, name

---- 结果示例 ----

program_id | name    | type | value  | last_modified_date
­­­­­­­­­­­­-------------------------------------------------------------
-> 00470   | Default | PPL  | 4.6900 | "2014-02-17 16:13:16" (same but different value)
-> 00470   | Default | PPL  | 0.0000 | "2014-02-19 16:25:11" (same but different value)
00470      | G1      | PPL  | 5.0000 | "2014-02-19 16:25:11"
00470      | G2      | PPL  | 3.5000 | "2014-02-19 16:25:11"
00015      | Default | PPL  | 0.0000 | "2014-02-19 16:25:11"
00015      | Default | PPL  | 0.0000 | "2014-02-19 16:25:11"

感谢您的宝贵时间和帮助!

Answer 1

您缺少 last_modified_date 字段的 select 语句，但无论如何，请将您的 select 语句更改为:

SELECT
    LPAD(convert( master_program.program_id USING utf8),
            5,
            '00000') AS program_id,
    rate.name,
    rate.type,
    rate.value,
    MAX(table.last_modified_date)
WHERE....