SQL Query - selecting higher priority rows more often

Question

I am trying to do SQL code in mysqli query to select rows with higher priority more often. I have a DB where all posts are sorted by priority, but I want it select like this (10 - the highest priority):

**Priority**
10
3
10
9
7
10
9
1
10

How can I do this? I have tried that to solve by more ways but no result. Thank you.

I don't understand the question. Does your table start with 9 rows? Why not just take the highest priority items using `order by`? Is the same priority 10 row being chosen 4 times? — Gordon Linoff, Aug 22 '18 at 19:39

score 1 · Answer 1 · answered Aug 22 '18 at 19:21

If you want to sample your data with preference to higher priorities, you could do something like this:

SELECT *
FROM (
    SELECT OrderDetailID
        ,mod(OrderDetailID, 10) + 1 AS priority
        ,rand() * 10 AS rand_priority
    FROM OrderDetails
    ) A
WHERE rand_priority < priority
ORDER BY OrderDetailID

This query runs in MySQL Tryit from W3Schools.

mod(OrderDetailID, 10) + 1 simulates a 1-10 priority - your table just has this value in it already

rand() * 10 gives you a random number between 0 and 10

Then by filtering to only ones where the random number is less than the priority, you get a result set where the higher priorities are more likely.

score 0 · Answer 2 · answered Aug 22 '18 at 19:12

You may use rank function if your MySQL version supports it. It will order your data by priority in descending order and ranks each row. If the two rows have same priority then both rows will have same ranking. Then you can filter out the first rank data which will give you highest priority rows always.

Select * FROM
(
    SELECT
      col1,         
      col2,
      priority,
      RANK() OVER w AS 'rank'         
    FROM MyTable
    WINDOW w AS (ORDER BY priority)
) MyQuery
Where rank = 1

Note : Syntax might be incorrect, please feel to edit the query.

This post might help you for ranking if your MySql version doesn't support Rank.

SQL Query - selecting higher priority rows more often

2 Answers2