Скачать или смотреть How to Retrieve a Random Row for Each Group in MySQL

Discover the solution to obtaining a random row for each group using MySQL with easy-to-follow SQL queries and explanations.
---
This video is based on the question https://stackoverflow.com/q/69550354/ asked by the user 'netAction' ( https://stackoverflow.com/u/589531/ ) and on the answer https://stackoverflow.com/a/69550551/ provided by the user 'Akina' ( https://stackoverflow.com/u/10138734/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Take random row for GROUP BY

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Retrieve a Random Row for Each Group in MySQL

In the world of databases, especially when working with SQL and MySQL, sometimes we need to accomplish tasks that appear simple but turn out to be a bit tricky. One common problem developers face is retrieving a random row for each group when using the GROUP BY clause. If you’ve stumbled upon this challenge, you’re not alone! Many have found that standard methods often yield predictable or unwanted results—such as always returning the first row for each group rather than a random one.

The Problem: Retrieving a Random Row for Each Group

Imagine you have a table called textelements containing various entries identified by a textid. Your goal is to pull one random row for each textid. However, after trying to execute a query that sorts the entries randomly and groups them, you find that it still returns consistently the first row instead. So what can you do to effectively retrieve a truly random row for each unique textid?

The Solution: Using Common Table Expressions

To overcome this issue in MySQL, you can utilize a Common Table Expression (CTE) along with the ROW_NUMBER() window function. This approach generates a unique row number for each row within its respective group, sorted randomly. Here’s a step-by-step breakdown of the solution.

Step-by-Step Breakdown

Create the CTE: Begin by defining a Common Table Expression that selects all columns from the textelements table. You'll use ROW_NUMBER() to assign a random number to each row per textid group.

Partitioning: Use the PARTITION BY clause to ensure that the row number resets for each unique textid while ordering the rows randomly with ORDER BY RAND().

Filter Results: After creating the CTE, query it to select only those rows where the row number equals 1—this will fetch one random row per textid group.

SQL Query Example

Here’s how the full SQL query will look:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

WITH cte AS: This line initiates the Common Table Expression.

SELECT * FROM textelements: This selects all fields available in the textelements table.

ROW_NUMBER() OVER (PARTITION BY textid ORDER BY RAND()) rn: This creates an ordered list of rows, with each row getting a unique number (rn) resetting for each textid, sorted randomly.

SELECT * FROM cte WHERE rn = 1: Finally, this query fetches all columns from the CTE where the row number is 1, ensuring every returned group of textid consists of only one random entry.

Conclusion

By using the above method, you can efficiently retrieve a random row for each group in your MySQL database, eliminating the frustration of unwanted results. This approach of utilizing Common Table Expressions and the ROW_NUMBER() function not only adds clarity to your queries but also enhances their performance.

Now, the next time you’re faced with the task of extracting random entries grouped by a certain identifier, you can confidently apply this SQL technique and retrieve precisely what you need!