MySQL select DISTINCT by highest value

Question

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

Ive tried

    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.

UPDATE:

schema

-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

This might be easier to answer if we could see the schema for those two tables. — Zagor23, Jul 11 '12 at 14:13
Yup... Sorry... copy/paste gone bad, still schema would be nice, to know how to perform join table onto itself, and what types of data you're dealing with. — Zagor23, Jul 11 '12 at 14:24
Schema added, this feels like a really simple question but I can't get the DISTINCT / JOIN part right. — BobFlemming, Jul 11 '12 at 14:28

ypercubeᵀᴹ · Accepted Answer · 2012-07-11T14:59:31.410

12

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 ;

edited Jul 11 '12 at 14:59

answered Jul 11 '12 at 14:17

ypercubeᵀᴹ

113,259
19
174
235

Thanks. This only returns the very latest though, not the latest of each issue. – BobFlemming Jul 11 '12 at 14:23
I don't see an `issue` column in your table. – ypercubeᵀᴹ Jul 11 '12 at 14:29
There isn't I just mean that the table contains issues. – BobFlemming Jul 11 '12 at 14:31
Where? In which column is this data? – ypercubeᵀᴹ Jul 11 '12 at 14:31
The table is a table full of 'issues'. So , issue 1 , issue 2 etc although its not defined like that , its defined by 'onsale' . – BobFlemming Jul 11 '12 at 14:33
So, what is my query returning? Not what you want? – ypercubeᵀᴹ Jul 11 '12 at 14:40
Yours was only returning 1 result. – BobFlemming Jul 11 '12 at 14:53
Oh boy, yes, I mixed the `magazine` and the `title` columns. Try this edit. – ypercubeᵀᴹ Jul 11 '12 at 14:59
an explanation would be incredibly useful for other people like myself who are also interested in accomplishing the same thing :( – oldboy Apr 21 '18 at 03:57
@Anthony this is a 6 years old question but I'll try to update it during the weekend. – ypercubeᵀᴹ Apr 21 '18 at 10:41
i know. i was just letting you know that an explanation with your answer would have made it 100 times more helpful :) – oldboy Apr 23 '18 at 02:36

score 10 · Answer 2 · answered Jul 11 '12 at 14:49

10

SELECT id, MAX(onSale) as latest, magazine
FROM product
GROUP BY magazine
ORDER BY latest DESC

answered Jul 11 '12 at 14:49

Jeff

908
2
9
23

1

This may show `id` and `onSale` that don't actually match in the table. – ypercubeᵀᴹ Jul 11 '12 at 15:01
1

@ypercube is correct. Do you need id for anything? Since it is not being aggregated or included in the GROUP BY clause, it can give you inconsistent results – Jeff Jul 11 '12 at 15:07
@ypercube Jeff You are both right. The results are incorrect , as the id and onSale do not relate to the same . – BobFlemming Jul 12 '12 at 11:21
I downvoted because you need to add a comment that points out that the id and onSale might not match in the results, I will remove my downvote. – Dave Scotese Apr 07 '16 at 17:13

Dan · Answer 3 · 2021-02-04T16:20:12.450

3

None of the given answers are correct, as they return an disassociated set of data that does not represent one exact row. The id may not be the id from the same row as the onsale value.

The following will work:

SELECT
    id, onsale, magazine
FROM (
    SELECT
       id, onsale, magazine
    FROM
        product
    ORDER BY
        onsale DESC) AS a
GROUP BY
    magazine

edited Feb 04 '21 at 16:20

answered Aug 02 '19 at 03:55

Dan

473
3
7

Zagor23 · Answer 4 · 2012-07-11T14:52:04.767

2

This looks like what you need:

SELECT id, MAX(onsale) AS onsale, magazine FROM magazines GROUP BY magazine ORDER BY onsale DESC;

Check it out at:

http://sqlfiddle.com/#!2/38e78/3

UPDATE: I've changed query a little, to return MAX(onsale)

edited Jul 11 '12 at 14:52

answered Jul 11 '12 at 14:38

Zagor23

1,953
12
14

Beaten to it by Jeff , literally by seconds! – BobFlemming Jul 11 '12 at 14:52

score -1 · Answer 5 · edited Jul 11 '12 at 13:59

-1

You need to put the 'rest of data' in the first select:

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

edited Jul 11 '12 at 13:59

Iwo Kucharski

3,735
3
50
66

answered Jul 11 '12 at 13:52

squiter

5,711
4
24
24

MySQL select DISTINCT by highest value

5 Answers5

Linked