How to display random results within selection

[mcmuney]

I'm using the code below, which displays the most recent 15 results. I'd like to modify it so that it displays the most recent 15 results in random order. How can I achieve this within the code below without having to use another query?

SELECT mem_id,type_id,date_added,value FROM sc_coins_asset WHERE value<>'0' ORDER BY date_added DESC LIMIT 0,15

[hansford]

You can try this, untested. There may be more robust ways of doing this. See if others chime in.

"SELECT mem_id,type_id,date_added FROM(SELECT value FROM sc_coins_asset WHERE value<>'0' ORDER BY RAND() DESC LIMIT 0,15) u ORDER BY date_added"

[Jacques1]

Just use shuffle() in your application. There's no reason for doing this in MySQL.

 

hansford's query is incorrect, because it yields 15 random rows which are then ordered by date (the selected fields also don't match). You want the reverse order of operations. But then again, why use MySQL in the first place?

[benanamen]

There's no reason for doing this in MySQL.

 

I disagree. You should ALWAYS have the database do the work when it can.

[QuickOldCar]

It's not good to do ORDER BY RAND() especially if have many rows, even if properly indexed is slow.

 

It's only 15 latest results so wouldn't matter much either way.