CODEWITHSUNDEEP
mysqlrandomsql-order-by
Seif
Seif

Reputation: 15

MySQL Select ordered rows, then randomize the result

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?

Upvotes: 0

Views: 44

Answers (1)

Barmar
Barmar

Reputation: 781058

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()

Upvotes: 1

Related Questions