CODEWITHSUNDEEP
mysql
whamsicore
whamsicore

Reputation: 8700

Difficult MySQL self-join please explain

This is supposed to be able to return a random post id. It is also said to be the fastest method using MySQL. 

SELECT t.id 
FROM table t 
JOIN (SELECT(FLOOR(max(id) * rand())) AS maxid FROM table) 
AS tt 
ON t.id >= tt.maxid 
LIMIT 1

Can't seem to wrap my head around this, please help.

Upvotes: 1

Views: 276

Answers (2)

Nanne
Nanne

Reputation: 64399

You pick a random ID number from your table with the "joined table"

 SELECT(FLOOR(max(id) * rand())) AS maxid FROM table

This will only return something you can use if the id's don't have holes in them (consequtive integers). THat's why you're joining on this

 ON t.id >= tt.maxid

You'll get all the id's that are HIGHER then your random number. The limit then gets the first of those.

So an example: say you have these entries in table: 1, 2, 5, 7, 8.

The floored random number will be smaller then 8, e.g. 3. You'll join will give you 5, 7 and 8 but returns only 5 because of the limit

Upvotes: 2

Alexander Malakhov
Alexander Malakhov

Reputation: 3529

First, formatting helps

1. SELECT t.id 
2.   FROM table t 
3.          JOIN (SELECT(FLOOR(max(id) * rand())) AS maxid FROM table) AS tt 
4.          ON t.id >= tt.maxid 
5. LIMIT 1

Let's look at subquery inside JOIN first:

  • rand() returns random value in range [0, 1] (e.g. 0.999, 0.251, 1)
  • max(id) means that your query will return maximal value of column id from specified table
  • Floor rounds input number down to the nearest integer (e.g. Floor(5.1) == 5, Floor(5.9) = 5)

hence, the subquery will return you random integer (due to FLOOR) number in the
range [0, 1]

"LIMIT 1" means that your query will return only one row from resulting set.

Hope this enough. Feel free to ask more details

Upvotes: 0

Related Questions