CODEWITHSUNDEEP
mysqlselectdistinct
user3052268
user3052268

Reputation: 21

SELECT DISTINCT picture, ID from Table

I need to be able to select 3 distinct picture URL's held in the database but also pull the other data in the row without it affecting the DISTINCT argument...

EXAMPLE::

DataBase =

ID - PICTURE_URL
01 - domain.com/pic1.jpg
02 - domain.com/pic1.jpg
03 - domain.com/pic1.jpg
04 - domain.com/pic2.jpg
05 - domain.com/pic3.jpg

So in the above example the SQL should only PULL either ID 1, 2, or 3 as a result, and 4 & 5 as the other 2 results.

OUTPUT = 

01 Domain.com/pic1.jpg
04 Domain.com/pic2.jpg
05 Domain.com/pic3.jpg

OR

02 Domain.com/pic1.jpg
04 Domain.com/pic2.jpg
05 Domain.com/pic3.jpg

OR

03 Domain.com/pic1.jpg
04 Domain.com/pic2.jpg
05 Domain.com/pic3.jpg

Because picture_url all had the same url to a picture stored on the server, so I do not want to select the same 3 pictures.

:)

Any help on this would be very much appreciated :)

Upvotes: 1

Views: 222

Answers (2)

slaakso
slaakso

Reputation: 9070

You can use aggregate function:

select min(ID), PICTURE_URL
from your_table
group by PICTURE_URL

Upvotes: 1

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT t1.ID,
       t1.PICTURE_URL
FROM Table1 t1
left join Table1 t2
on t1.PICTURE_URL = t2.PICTURE_URL
AND t1.ID > t2.ID
WHERE t2.ID is null

Result:

| ID |         PICTURE_URL |
|----|---------------------|
|  1 | domain.com/pic1.jpg |
|  4 | domain.com/pic2.jpg |
|  5 | domain.com/pic3.jpg |

Upvotes: 0

Related Questions