CODEWITHSUNDEEP
phpmysqlsqlrandom
Robert Ames
Robert Ames

Reputation: 51

SQL Select In Random Order BUT Do Not Select Duplicate From A Specifc Column

Ok so I have my database that has multiple ads...BUT I have multiple ads from the same site that have been added....here is an example.

enter image description here

As you can see...the database has two links that are different...but they lead to the same site. My goal is to have it RANDOMLY select one of them and display it but not the other WHILE ALSO selecting all of the others and displaying those as well. Here is the coding I have at the moment for it:

<?php
$query = 'SELECT * FROM `ads`';
        $results = mysqli_query($connect, $query) or die(mysql_error());
        while ($row = mysqli_fetch_array($results, MYSQLI_ASSOC)) {
            extract($row);
            echo '
                <div class="col-sm-6">
                <div class="card mb-30"><a class="card-img-tiles" href="'.$link.'">
                    <div class="inner">
                      <div class="main-img"><img src="'.$image.'" alt="'.$alt_title.'"></div>
                    </div></a>
                  <div class="card-body text-center">
                    <h4 class="card-title">'.$name.'</h4>
                    '.$about.'
                    <a class="btn btn-outline-primary btn-sm" href="'.$link.'">Check It Out</a>
                  </div>
                </div>
              </div>
            ';
            }
?>

I want it to display the circled ones randomly selected but not both for each set.

enter image description here

Upvotes: 0

Views: 128

Answers (1)

GMB
GMB

Reputation: 222492

Starting with version 8.0 of MySQL (or MariaDB 10.2), you can select one random record in each group of records having the same mainsite using ROW_NUMBER(), and then sort randomly in the outer query with ORDER BY RAND()

SELECT * FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(PARTITION BY mainsite ORDER BY RAND()) rn
    FROM ads t
) x WHERE rn = 1
ORDER BY RAND()

With earlier versions of MySQL / MariaDB, this gets tedier. One solution is to use a pair of variables to track groups and assign row numbers :

SELECT *
FROM (
    SELECT 
        a.*,
        CASE
            WHEN @mainsite != mainsite THEN @rn := 1
            ELSE @rn := @rn + 1
        END rn,
        @mainsite := mainsite
        FROM 
            (SELECT * FROM ads ORDER  BY RAND()) a
            CROSS JOIN (SELECT @rn := 0, @mainsite := NULL) r
        ORDER  BY a.mainsite
    ) s
WHERE  rn <= 1
ORDER  BY RAND()

Upvotes: 1

Related Questions