devtreat devtreat - 1 year ago 45
MySQL Question

Mysql get 1 value from duplicates

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 |

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download