MySQL按最高值选择DISTINCT [英] MySQL select DISTINCT by highest value

问题描述

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

I have a table full of magazines, and need to extract the latest unique issue of each magazine.

我尝试过

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

哪个返回不同的杂志，但不返回我需要的其余数据.

Which returns the distinct magazines , but not the rest of the data I require.

更新:

模式

-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

所以我想要返回的结果是:

So the result I would like returned would be:

 -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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！