Mysql get 1 value from duplicates

Question

I made a query where I obtain duplicate values from a table X

id         | name    |     created         | modified 
-----------------------------------------------------------
 2         | Film1   | 2016-01-29 06:00:09 | 2016-01-29 06:00:10
 5         | Film1   | 2016-01-27 06:00:09 | 2016-01-27 06:00:10
 7         | Film1   | 2016-01-23 06:00:09 | 2016-01-23 06:00:10
 1         | Film2   | 2016-01-26 06:00:09 | 2016-01-26 06:00:10
 8         | Film2   | 2016-01-28 06:00:09 | 2016-01-28 06:00:10
 9         | Film2   | 2016-01-29 06:00:09 | 2016-01-29 06:00:10

I have a table Y where I obtain the number of photos of each duplicate value:

id         | name    |     num_photos      | 
--------------------------------------------
 2         | Film1   |        20           | 
 5         | Film1   |        10           | 
 7         | Film1   |        12           | 
 1         | Film2   |        20           | 
 8         | Film2   |        50           | 
 9         | Film2   |        12           |

How can I get the duplicated with more number of photos? IN a php array?

Result:

id         | name    |     num_photos | 
---------------------------------------
 2         | Film1   |        20      |  
 8         | Film2   |        50      |

MAX(num_photos) and GROUP BY name should help

Sougata Bose
– Sougata Bose

2017-02-07 13:12:23 +00:00
Commented Feb 7, 2017 at 13:12 — Sougata Bose
– Sougata Bose, Commented Feb 7, 2017 at 13:12
See meta.stackoverflow.com/questions/333952/…

Strawberry
– Strawberry

2017-02-07 13:17:47 +00:00
Commented Feb 7, 2017 at 13:17 — Strawberry
– Strawberry, Commented Feb 7, 2017 at 13:17

Raymond Nijland · Accepted Answer · 2017-02-07 13:46:45Z

Create table/insert data

CREATE TABLE Y
    (`id` INT, `name` VARCHAR(5), `num_photos` INT)
;

INSERT INTO Y
    (`id`, `name`, `num_photos`)
VALUES
    (2, 'Film1', 20),
    (5, 'Film1', 10),
    (7, 'Film1', 12),
    (1, 'Film2', 20),
    (8, 'Film2', 50),
    (9, 'Film2', 12)
;

There are two ways off doing this.

With user variables.

Query

SELECT 
   y.id
 , y.name
 , y.num_photos
FROM ( SELECT @name := '') AS init_user_var
CROSS JOIN (
 SELECT
  *
  , (y.name != @name) AS firstInGroup
  , @name := y.name
 FROM 
  Y
 ORDER BY 
    Y.name ASC
  , Y.num_photos DESC 
)
 AS
  Y   
WHERE
 y.firstInGroup = 1
ORDER BY 
 Y.id ASC

Result

    id  name    num_photos  
------  ------  ------------
     2  Film1             20
     8  Film2             50

With delivered table with max and group by and a join.

Query

SELECT 
 y.*
FROM (
 SELECT 
   y.name 
  , MAX(y.num_photos) max_num_photos
 FROM 
  Y
 GROUP BY
  y.name 
) AS
 y_max_num_photos
INNER JOIN
  Y
ON
 Y.name = y_max_num_photos.name
 AND
 Y.num_photos = y_max_num_photos.max_num_photos
 ORDER BY 
  y.id ASC

Result

    id  name    num_photos  
------  ------  ------------
     2  Film1             20
     8  Film2             50

Anton · Accepted Answer · 2017-02-07 13:12:48Z

2

It could be something like:

select * from (select * from y order by num_photos desc) sub group by name

Please adjust it a little by your needs.

answered Feb 7, 2017 at 13:12

Anton

1,0518 silver badges21 bronze badges

3 Comments

Raymond Nijland Over a year ago

SELECT * WITH GROUP BY name is not correct... psce.com/blog/2012/05/15/…

Anton Over a year ago

so.. should he write the column names in place of *? will it change the answer direction ? ;)

Strawberry Over a year ago

This relies on an undocumented hack (unless you count the comments section of the manual), and should not be relied upon.

JoshulSharma · Accepted Answer · 2017-02-07 13:52:42Z

-2

Try below query.

 SELECT DISTINCT(name) as name, (select max(num_photos) from Y) as num_photos, id FROM Y;

edited Feb 7, 2017 at 13:52

answered Feb 7, 2017 at 13:15

JoshulSharma

1737 bronze badges

5 Comments

Anton Over a year ago

what about id? :)

Loko Over a year ago

@Anton What do you mean id?

JoshulSharma Over a year ago

@Anton happy now?

Anton Over a year ago

@JoshulSharma, Dont be mad, I haven't downvoted you, if you think so. But, what do you think he will get with your query? I think it will be the same row count as the table have, with the first field as max photos always, and this information doesnt tell anything related to what the author is asking for. Am i wrong?

JoshulSharma Over a year ago

@Anton not mad at you! its just your interpretation. :)

Collectives™ on Stack Overflow

Mysql get 1 value from duplicates

3 Answers 3

Comments

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related