Fetch Record Randomly according priorty

mysql, php

Solution

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

Problem

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.

Original source