CODEWITHSUNDEEP
mysqlsql
Tony Hanks
Tony Hanks

Reputation: 114

Show First instance of records with duplicate values in colum

I have these values in a table called galley_images:

id    gallery_id    image_id
1             1            1
2             1            2
3             1            3
4             2            4 
5             2            5 
6             2            6 
7             2            7
8             3            8

I want to return the results like this:

id    gallery_id    image_id
1             1            1
4             2            4
8             3            8

Basically I only want the first instance of any record with a unique gallery_id.

I have a solution using Access DB but need a solution for MySQL, Access solution was:

SELECT id, First(gallery_id) AS FirstOfgallery_id,  
           First(image_id) AS FirstOfimage_id
FROM galley_images 
GROUP BY id

Any help?

Upvotes: 5

Views: 8813

Answers (4)

RustamIS
RustamIS

Reputation: 697

Some additional to John Woo's solution:

SELECT  a.*
FROM    gallery_images a
    INNER JOIN
    (
        SELECT gallery_ID, MIN(id) id
        FROM gallery_images 
        GROUP BY gallery_ID
    ) b
        ON a.gallery_ID = b.gallery_ID AND
            a.id = b.id

we must get by minimum record id's not by min(image_id) cause image_id can be coming in different order.

Upvotes: 2

John Woo
John Woo

Reputation: 263723

The idea behind the subquery is to select the minimum ID for each gallery_ID. Then join it against itself to get all the columns of the table (if any).

SELECT  a.*
FROM    gallery_images a
        INNER JOIN
        (
            SELECT gallery_ID, MIN(image_ID) minID
            FROM gallery_images 
            GROUP BY gallery_ID
        ) b
            ON a.gallery_ID = b.gallery_ID AND
                a.image_ID = b.minID

Upvotes: 3

Salil
Salil

Reputation: 47482

SELECT id, gallery_id, image_id FROM galley_images WHERE id IN (
        SELECT MIN(ID) 
        FROM gallery_images 
        GROUP BY gallery_ID
    )

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

    SELECT * 
  FROM   galley_images t
  JOIN 
       ( SELECT gallery_id,
                MIN(image_id) as image_id
         FROM   galley_images
         GROUP BY gallery_id )a
  ON   t.gallery_id    =a.gallery_id    
  AND  t.image_id=a.image_id


SQL Fiddle Demo

Upvotes: 1

Related Questions