77189

Mysql order by RAND, but must include few data

<h3>Question</h3>

I am facing some unusual issue. Please help. There are 300 rows in a column. I want to display any 100 order by rand(). But within this randomly selected 100, there must be 2 rows. How can I right it? Example:

"Select id from sample_table where id<300 or id>1 order by rand() limit 100"

But I want the result must include id=34 and id=78


<h3>Answer1:</h3>

Use UNION ALL to select the 2 rows that must exist in the results and 98 random rows:

select id from sample_table where id in (34, 78) union all select id from ( select id from sample_table where where id not in (34, 78) order by rand() limit 98 ) t order by rand()

or simpler with conditional sorting:

select * from ( select id from sample_table order by id not in (34, 78), rand() limit 100 ) t order by rand()
<h3>Answer2:</h3>

Union both to the original SELCT and random the result of that union

SELECT id FROM (SELECT 34 UNION SELECT 78 UNION SELECT id FROM sample_table WHERE id < 300 OR id > 1 ORDER BY RAND() LIMIT 98) ORDER BY RAND()

来源:https://stackoverflow.com/questions/61431846/mysql-order-by-rand-but-must-include-few-data

Recommend

  • Color axis spine with multiple colors using matplotlib
  • How do I detect if an email client is configured on an Android device?
  • ffmpeg: How to concat audio files and add background music in a single command?
  • GLConsumer is already attached to a context for new SurfaceTexture
  • ggplot2 facet_grid with distinct x-axis labels using facet_grid
  • Implementing and using MinMax with four in row (connect4) game
  • When i select a Textfield the keyboard moves over it
  • Nested AJAX Calls using .done
  • Making Django.contrib.auth store plain-text password
  • How to create mirrored image effect with CSS single element
  • Using loops in Jasmine (with injected service)
  • How to put an object in the air?
  • how can i get selectedRange.location value?
  • Getting the type of a “Type” in C# reflection
  • Regex not working in java 1.5
  • How to check if a database and tables exist in sql server in a vb .net project?
  • Runtime complexity of getting the length of a string in different representations
  • Calculate time from document
  • 'url' requires a non-empty first argument. The syntax changed in Django 1.5, see the docs
  • Error handeling in antlr 3.0
  • JavaScript Regex to Match Boundaries of Words with diacritics
  • Amazon Elastick BeanStalk error: Failed to create the AWS Elastic Beanstalk application version
  • How do I add a mouse over tooltip to an Image using .DrawImage()
  • Using redis as an LRU cache for postgres
  • Functions by reference or by variable, which to use when?
  • Unity3d lost directional light shadows after generate assetBundle (.unity3d file)
  • Bad automatic Triangulation with Mayavi for coloring a surface known only by its corner
  • Apple Mach-O Linker error (“duplicate symbol”)
  • Update cell query for Excel ADO from Delphi
  • How to get rgb from transparent pixel in js
  • Computing the discrete fourier transform of audio data with FFTW
  • How to use FirstOrDefault inside Include
  • PHP Permalinks.. how to change?
  • media foundation H264 decoder not working properly
  • Running R's aov() mixed effects model from Python using rpy2
  • Access to a Matlab gui from the web
  • ReferenceError: TextEncoder is not defined