Fetch Record Randomly according priorty

Question

I have a table I want to fetch record according priority and Date wise which I getting by this query

  SELECT * 
    FROM tbadv 
   WHERE advstrdat < CURDATE() 
ORDER BY advenddat DESC,
         advpri=1 DESC,
         advpri=2 DESC,
         advpri=3 DESC,
         advpri=4 DESC,
         advpri=5 DESC 
   LIMIT 1

But if multiple record exist with same priority then I want randomly record from it.

Tell me which I prefer between ORDER BY RAND() or use JOIN.

These are the column of my Table:

advid
advtit
advdes
advimg
advurl
advloc
advstrdat
advenddat
advpri

Any help, suggestion would be very much appreciated.

Answer 1

It is not too clear how do you want to resolve ties.

Anyway:

SELECT * 
   FROM tbadv 
   WHERE advstrdat < CURDATE() 
ORDER BY advenddat DESC, advpri BETWEEN 1 AND 5 DESC, advpri DESC, RAND()
   LIMIT 1

This will retrieve the latest records based on advenddat, according to their date, regardless of priority, i.e. a low priority record which is newer than a higher priority record will go first (so, check that advenddat is a date and not a datetime or, if it is, it is initialized with a date. Otherwise you'll only very rarely have records with the same advenddat, to be sorted based on the other fields).

Then those records with priority between 1 and 5 will be chosen. Among these, those with higher priority will go first.

If two records have the same advenddat and the same advpri between 1 and 5 (say, 3), then the tie will be broken at random.

For performance reasons you will want to have a covering index on advstrdat, advenddat, advpri in this order:

CREATE UNIQUE INDEX tbadv_ndx ON tbadv ( advstrdat, advenddat, advpri, advid );

Best performances if table is large, and many records are matched, can be achieved by decoupling record choice (which only needs the primary key) and actual record retrieval:

SELECT tbadv.*
    FROM tbadv
    JOIN ( 
        SELECT advid
            FROM tbadv
            WHERE advstrdat < CURDATE()
           ORDER BY advenddat DESC, advpri BETWEEN 1 AND 5 DESC, advpri DESC, RAND()
        LIMIT 1 ) AS p
    USING ( advid );

+----+-------------+------------+--------+---------------+-----------+---------+-------+-------+-----------------------------------------------------------+
| id | select_type | table      | type   | possible_keys | key       | key_len | ref   | rows  | Extra                                                     |
+----+-------------+------------+--------+---------------+-----------+---------+-------+-------+-----------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL      | NULL    | NULL  |     1 |                                                           |
|  1 | PRIMARY     | tbadv      | const  | PRIMARY       | PRIMARY   | 4       | const |     1 |                                                           |
|  2 | DERIVED     | tbadv      | range  | tbadv_ndx     | tbadv_ndx | 4       | NULL  | 60924 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+------------+--------+---------------+-----------+---------+-------+-------+-----------------------------------------------------------+

The above is a sample table with around 130 thousand rows, pretty unbalanced (all records have valid curdate, all have valid priority, etc.). Nevertheless, it completes within milliseconds.

"Invalid" records get filtered quickly, so the performances are equivalent to those of a single query, except that only advid is retrieved: here, only seven records match:

+----+-------------+------------+--------+---------------+-----------+---------+-------+------+-----------------------------------------------------------+
| id | select_type | table      | type   | possible_keys | key       | key_len | ref   | rows | Extra                                                     |
+----+-------------+------------+--------+---------------+-----------+---------+-------+------+-----------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL      | NULL    | NULL  |    1 |                                                           |
|  1 | PRIMARY     | tbadv      | const  | PRIMARY       | PRIMARY   | 4       | const |    1 |                                                           |
|  2 | DERIVED     | tbadv      | range  | tbadv_ndx     | tbadv_ndx | 4       | NULL  |    7 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+------------+--------+---------------+-----------+---------+-------+------+-----------------------------------------------------------+

Different order

If you wanted to give advpri priority over advenddat, i.e., a record with priority 5 expiring today has to be given priority over a record with priority 4 even if it expires next year, you'd just have to invert the position of advpri and advenddat:

ORDER BY advpri BETWEEN 1 AND 5 DESC, advpri DESC, advenddat DESC, RAND()

Now priorities 1-5 go first, then priorities 0, 6, 7 and other outside the 1-5 range; and they go highest priority (i.e. 5) first. If two records have the same priority, then the one with longer life expectancy goes first. Only when life expectancy is the same, the choice is at random. (You also must swap fields position in the index creation statement).