CODEWITHSUNDEEP
mysqlselectdistinct
Alan Mulligan
Alan Mulligan

Reputation: 1198

MYSQL SELECT DISTINCT value from and column and its corresponding value from another

I have a column in a database which i use the below code to get the distinct values from that column

"SELECT DISTINCT category FROM galleryinfo"

I have another column with the path to the relevant image.

So my question is how do i get the unique/distinct values from category and it corresponding path from the other column.

Thanks in advance for any help. 

id    path           category
1     img1           car
2     img2           car
3     img3           truck
4     img4           truck
5     img5           bike

The result i require would be

img1   car     
img3   truck   
img5   bike

Upvotes: 2

Views: 1102

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562553

You can use a GROUP BY query to reduce the result to one row per distinct category:

SELECT category, path FROM galleryinfo GROUP BY category

The problem with this is that it's arbitrary which path this query returns. In fact, in most SQL implementations, this is an illegal query and results in an error. MySQL is more lenient, but it will return an arbitrary path from those matching the category (in practice, it chooses the path from the first row with respect to physical storage, but this is not documented or guaranteed).

You can choose either the MIN(path) or MAX(path), or concatenate all paths per category together into one string with GROUP_CONCAT(path), but that's about it.

Upvotes: 1

Related Questions