How to select Distinct with random column MYSQL?

Question

I've table books that contains image, language, faith. I'am getting distinct values for faith with

SELECT DISTINCT faith, language from books;

That fetches faith series (15 results) but I want to fetch image along too but randomly. So the result can be able to show faith with random image. How I can do that, please suggest.

This will involve two queries, or one especially ugly query. — tadman
– tadman, Commented May 2, 2016 at 7:35
You get distinct values for faith AND language with that query. — jarlh
– jarlh, Commented May 2, 2016 at 7:57
two SELECTS or Queries? could you please mention a sample. — RanaHaroon
– RanaHaroon, Commented May 2, 2016 at 7:58
So this query selects distinct faith-language combinations found in the table. And per result row you want an image. Just any of the whole table? Or any matching the faith? Or any matching the language? Or any matching both faith and language? — Thorsten Kettner
– Thorsten Kettner, Commented May 2, 2016 at 10:44
And "randomly" means everytime you run the query, it should be different images picked? Or does it mean you just don't care which, and you'd be fine with the query picking the same images every time? — Thorsten Kettner
– Thorsten Kettner, Commented May 2, 2016 at 10:53

Thorsten Kettner · Accepted Answer · 2016-05-02 11:06:14Z

2

The query

select distinct faith, language from books;

selects distinct Faith-Language combinations from your table. So where your table contains two entries for the same Faith and Language

Faith  Language  Image
1      EN        img1
1      EN        img2
1      FR        img3
1      FR        img4

your result would contain each combination just once:

Faith  Language
1      EN
1      FR

It is not clear from your question yet, what you mean with a random image. Here is how you get one of the matching images for a Faith-Language combination arbitrarily picked:

select faith, language, image
from books
group by faith, language;

The result could look like this:

Faith  Language  Image
1      EN        img2
1      FR        img3

The reason is that we aggregate the rows. In the GROUP BY clause we say we want one result row per Faith and Language, but we also select Image and don't specify which one we'd like to see per group (which we could do with an aggregate function like MIN or MAX), so we just get one of the images matching the Faith-Language combination arbitrarily picked by the DBMS.

Is this already what you want?

answered May 2, 2016 at 11:06

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RanaHaroon Over a year ago

i appreciate your input but this couldn't work please let me explain little more to understand. i have many rows in table books with different faith column. there's many BOOKS that are specified with faiths with it's own BOOK IMAGE. now i am fetching all the faiths from books table and i want to set a random image for the faith that can have 100 books against itself. each time when query is executed faith will remain same but image should be changed randomly each time for instance --(faith: religious | image=modesty.jpg) --(faith: religious | image=book_of_religions.jpg) leave language.

Community · Accepted Answer · 2017-05-23 12:00:59Z

0

Thank to everybody, i got the solution from

Select random row per distinct field value?

SELECT r.faith, 
(SELECT r1.image FROM books AS r1 WHERE r.faith=r1.faith ORDER BY rand() LIMIT 1) AS image FROM books AS r GROUP BY r.faith;

it's fetching distinct field1 faith with random field2 image against each faith everytime query executed.

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered May 7, 2016 at 10:35

RanaHaroon

4531 gold badge6 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to select Distinct with random column MYSQL?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related