MySQL按最高值选择DISTINCT

我有一张摆满杂志的 table ，需要提取每本杂志的最新特刊。

我试过了

    SELECT DISTINCT
    magazine
        FROM
    product p
        INNER JOIN
    (SELECT
        title, MAX(onSale) AS Latest
    FROM
        product
    GROUP BY magazine) groupedp

它返回不同的杂志，但不返回我需要的其余数据。

更新:

图式

-id----onsale----magazine
  1    1/12/12   Fishing Mag
  2    1/11/12   Fishing Mag
  3    12/03/11  Pencil Sharpening Monthly
  4    1/02/10   Pencil Sharpening Monthly
  5    16/04/09  Homes in the Sky

所以我想返回的结果是:

 -id----onsale----magazine
   1    1/12/12   Fishing Mag
   3    12/03/11  Pencil Sharpening Monthly
   5    16/04/09  Homes in the Sky

最佳答案

SELECT
    p.*
FROM
        product p
    INNER JOIN
        ( SELECT
              magazine, MAX(onSale) AS latest
          FROM
              product
          GROUP BY
              magazine
        ) AS groupedp
      ON  groupedp.magazine = p.magazine
      AND groupedp.latest = p.onSale ;

关于MySQL按最高值选择DISTINCT，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/11434124/