CODEWITHSUNDEEP
phpmysql
Funereal
Funereal

Reputation: 673

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      |

Upvotes: 1

Views: 59

Answers (3)

JoshulSharma
JoshulSharma

Reputation: 173

Try below query.

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

Upvotes: -2

Raymond Nijland
Raymond Nijland

Reputation: 11602

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

Upvotes: 2

Anton
Anton

Reputation: 1061

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.

Upvotes: 2

Related Questions