<p>I have 40 providers and 10,000 products, but I want to show 1 product from each provider</p> <table class="s-table"> <thead> <tr> <th>Brand</th> <th>Provider</th> <th>Products</th> <th>URL</th> </tr> </thead> <tbody> <tr> <td>Lightning</td> <td>Pragmatic Game</td> <td>Mrs. Destiny</td> <td>Link</td> </tr> <tr> <td>Lightning</td> <td>Isoftbet</td> <td>Halloween Jack</td> <td>Link</td> </tr> <tr> <td>Lightning</td> <td>Pragmatic Game</td> <td>Sweet Bonanza</td> <td>Link</td> </tr> <tr> <td>Lightning</td> <td>Isoftbet</td> <td>Tropical Security</td> <td>Link</td> </tr> <tr> <td>Lightning</td> <td>Network</td> <td>Royal Potato</td> <td>Link</td> </tr> <tr> <td>Lightning</td> <td>Network</td> <td>Mrs. Destiny</td> <td>Link</td> </tr> </tbody> </table> <p>This is my current SQL table.But I want to display 1 item per provider, for example: </p> <table class="s-table"> <thead> <tr> <th>Brand</th> <th>Provider</th> <th>Products</th> <th>URL</th> </tr> </thead> <tbody> <tr> <td>Lightning</td> <td>Pragmatic Game</td> <td>Mrs. Destiny</td> <td>Link</td> </tr> <tr> <td>Lightning</td> <td>Isoftbet</td> <td>Halloween Jack</td> <td>Link</td> </tr> <tr> <td>Lightning</td> <td>Network</td> <td>Royal Potato</td> <td>Link</td> </tr> </tbody> </table> <p>This is my code `</p> <pre class="brush:php;toolbar:false;"><?php /* Attempt MySQL server connection. Assuming you are running MySQL server with default setting (user 'root' with no password) */ $link = mysqli_connect("localhost", "newuser1", "p, Dn@auTD3$*G5", "newdatabse"); // Check connection if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); }// Attempt select query execution $sql = "SELECT * FROM tablename WHERE Brand='Coolcasino' and Provider IN ('Pragmatic Play','Isoftbet','Netent') ;"; if($result = mysqli_query($link, $sql)){ if(mysqli_num_rows($result) > 0){ echo "<table>"; echo "<tr>"; echo "<th>Brand</th>"; echo "<th>Provider</th>"; echo "<th>Product</th>"; echo "<th>URL</th>"; echo "</tr>"; while($row = mysqli_fetch_array($result)){ echo "<tr>"; echo "<td>" . $row['Brand'] . "</td>"; echo "<td>" . $row['Provider'] . "</td>"; echo "<td>" . $row['Product'] . "</td>"; echo "<td>" . $row['URL'] . "</td>"; echo "</tr>"; } echo "</table>"; // Close result set mysqli_free_result($result); } else{ echo "No records matching your query were found."; } } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } // Close connection mysqli_close($link); ?></pre> <p>如果有人可以的话请帮助我`</p>

$sql = "SELECT * FROM tablename WHERE Brand='Coolcasino' and Provider IN ('Pragmatic Play','Isoftbet','Netent') GROUP BY  Provider;";

select Brand,
       Provider,
       Product,
       URL
from (   select Brand,
                Provider,
                Product,
                URL,
                row_number() over(partition by Provider order by rand()) as row_num
         from tablename
         where Brand='Lightning' 
         and Provider IN ('Pragmatic Play','Isoftbet','Netent') 
      ) as rand_prod
where row_num=1;