MySQL Select ordered rows, then randomize the result

Question

I'm trying to fetch rows, ordered by ID ASC but I also want that result to be randomized afterwards. So basically, if I want to fetch the first 15 rows, I want them to come out randomized but as the first 15 rows.

Basically, I have a frame that loads the first 15 clients, and a button "load more" to load 15 more clients. I simply want the same 15 clients to come out by order ID, but have their positions randomized.

This is what I have thus far, but the RAND() at the end is not having any impact:

SELECT wp_posts.ID, wp_posts.post_title,
    wp_postmeta.meta_value,
    axess_clients.client_nom,
    axess_clients.site
FROM wp_posts 
LEFT JOIN axess_clients ON axess_clients.client_id = wp_posts.ID
LEFT JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID
WHERE wp_posts.post_type='clients' AND wp_posts.post_status='publish' 
AND wp_postmeta.meta_key='_thumbnail_id'
ORDER BY wp_posts.ID ASC, RAND() LIMIT 15 OFFSET ".$_POST['data']

Is there a way to do this via MySQL or do I really have to pull out PHP for this?

Barmar · Accepted Answer · 2017-09-01 19:27:41Z

1

When you have multiple expressions in ORDER BY, the second expression is used to order within a group where the first expression is equal. It can't reorder rows that are already ordered by the first expression.

To reorder something, you need to put the first ordering in a subquery.

SELECT *
FROM (
    SELECT wp_posts.ID, wp_posts.post_title,
        wp_postmeta.meta_value,
        axess_clients.client_nom,
        axess_clients.site
    FROM wp_posts 
    LEFT JOIN axess_clients ON axess_clients.client_id = wp_posts.ID
    LEFT JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID
    WHERE wp_posts.post_type='clients' AND wp_posts.post_status='publish' 
    AND wp_postmeta.meta_key='_thumbnail_id'
    ORDER BY wp_posts.ID ASC
    LIMIT 15 OFFSET " . $_POST['data']) AS x
ORDER BY RAND()

answered Sep 1, 2017 at 19:27

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Select ordered rows, then randomize the result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related