I’m in code-review right now, and happened to re-read this line of code…
ORDER BY subsection_slug, RAND()";
Is that correct?
And what exactly does it do?
When I look online, I see examples where people have ORDER BY rand(), but not where there are two variables in the ORDER BY.
If I had to guess, it first orders by “subsection_slug”, and then orders by some second field in random order, but I don’t know for sure?! :-/
Sincerely,
Debbie
jeepers, your sql really is rusty, isn’t it
what happened when you tested it? © ™
It runs, but if I understood how it works I wouldn’t be here…
My query starts off as…
$q2 = "SELECT ap.ds_subsection_slug, a.slug, a.heading, a.summary_short, a.image
So what is RAND() sorting on?
Since it is the 2nd argument in the ORDER BY, does that mean that it sorts on the 2nd field in my SELECT?
I wouldn’t assume that, because that’s not how regular queries work.
In a normal query, you have to specify the SORT FIELD and SORT ORDER for each argument in the ORDER BY.
And thus my question…
Debbie
RAND()
That’s a nonsensical response…
What FIELD is being sorted randomly?
Debbie
maybe to you 
actually, they’re called columns 
RAND() produces a random number, one for each row of the result set
these random numbers are in a column that you might consider “attached” to the columns that you’ve specified for the result set in the SELECT list
they are not sorted randomly, they are sorted ASC (by default, since you did not specify DESC)
Right.
You are saying that when I have RAND() in the ORDER BY clause, that MySQL assigns a random number to each row in like a “temporary column”?
When I run this query…
SELECT ap.ds_subsection_slug, a.slug, a.heading, a.summary_short, a.image
:
:
ORDER BY subsection_slug, RAND()
MySQL first sorts records by “subsection_slug” and then it sorts by this “temporary column” containing random values?
So maybe like this…
Before Sort:
Subsection Temporary Column Article
----------- ----------------- --------
Economy .987 which-states-support-main-street
Economy .125 sequester-could-lead-to-more-start-ups
Economy .048 best-cities-for-small-business
Markets .501 asian-stocks-rise-most-in-7-months-on-chinese-inflation
Markets .225 jc-penney-said-to-hire-blackstone-to-raise-1-billion
Taxes .913 save-your-taxes-for-a-cpa
Taxes .672 what-is-eftps
After Sort:
Subsection Temporary Column Article
----------- ----------------- --------
Economy .048 best-cities-for-small-business
Economy .125 sequester-could-lead-to-more-start-ups
Economy .987 which-states-support-main-street
Markets .225 jc-penney-said-to-hire-blackstone-to-raise-1-billion
Markets .501 asian-stocks-rise-most-in-7-months-on-chinese-inflation
Taxes .672 what-is-eftps
Taxes .913 save-your-taxes-for-a-cpa
Sincerely,
Debbie
what happened when you tested it? © ™
Let’s go over this again…
My query runs, but I have no way of knowing precisely what is going on in the Database Engine.
I have asked you for clarification on what you said a few times, yet you keep up with “What happens when you tested it” which is NOT an answer to my questions…
Debbie
rand() return column with a value between 0 and 1.0. so your query will order by subsection_slug first, if they are same values for subsection_slug, then order by the rand() column
and you have yet to report back on what you observed when you tested it
Obviously things are sorted by Subsection, and then the Articles are randomized HOWEVER I don’t know how they are being randomized…
I originally asked if they were being randomized on the 2nd field.
Then I asked you to clarify what you mean in your response.
Then you went to your favorite “What happened when you tested”
For all I can visually see, the records could be sorted on any number of fields and thus appear to be random.
You hold the answer.
Should I just keep guessing “randomly” as to what is happening?
Debbie
i prefer you keep testing
add RAND() AS rand_value to your SELECT list, run the query again, and inspect the values within each subsection, to see whether they actually do fall into ASC or DESC sequence, depending on what you have in the ORDER BY
then run it again and observe the different random numbers you get
then you tell me what you think is happening… and don’t guess but figure it out
SELECT subsection_slug, slug, RAND() AS rand_value, heading, summary_short, image
:
:
ORDER BY subsection_slug, RAND()
RAND() generates a random number, and I see the records sorted by subsection_slug, and then rand_value, so that implies the ORDER BY is on column #1 and then column #3 which doesn’t tell me much, since earlier you said that having RAND() in the ORDER BY creates a “temporary column” with random numbers.
I can see no reason to believe that the random numbers in rand_value are the same as ORDER BY subsection_slug, RAND()
My first guess at what would happen would be something like this…
Subsection rand_value Temporary Column Article
----------- ----------- ----------------- --------
Economy .521 .048 best-cities-for-small-business
Economy .186 .125 sequester-could-lead-to-more-start-ups
Economy .884 .987 which-states-support-main-street
Markets .905 .225 jc-penney-said-to-hire-blackstone-to-raise-1-billion
Markets .023 .501 asian-stocks-rise-most-in-7-months-on-chinese-inflation
Taxes .420 .672 what-is-eftps
Taxes .107 .913 save-your-taxes-for-a-cpa
I would say rand_value is independent of ORDER BY subsection_slug, RAND().
It’s one thing for me to ask, “Tell me how this works” and quite another to ask, “Did you mean this?”
Why you can’t verify if I understood you above is beyond me.
I am trying to learn…
Debbie