CODEWITHSUNDEEP
mysqlsql
Deepak ML
Deepak ML

Reputation: 79

Mysql ORDER BY RAND()

I have a table storing the usage count of topics used, for each subsequent request increases the usage count. I have added a query to return the least used topic. 

      SELECT b.body, n.nid
      FROM node n
      LEFT JOIN body b ON n.nid = b.entity_id
      LEFT JOIN topic_usage u ON n.nid = u.entity_id
      LEFT JOIN reference r ON n.nid = r.entity_id
        AND (
          r.entity_type =  'node'
        AND
          r.deleted =  '0'
        )
      WHERE n.type = :type
      AND n.status = 1
      AND r.target_id= :id
      ORDER BY u.field_topic_usage_value ASC LIMIT 0,1

Example table 

nid | Usage Count
-----------------
 1  | 0
 2  | 0
 3  | 0
 4  | 1

The above query works fine to return the lowest used topic. But the requirement is to apply RAND() on the lowest usage.

As per the above example table, the query should do a rand() where usage count is 0.

I can write 2 queries, first one gets the lowest count and second query does the rand() on that count but can that be combined into 1 ?

Thank you.

Solution 1 @fancypants

      SELECT b.body, node.nid
      FROM node 
      LEFT JOIN body ON n.nid = body.entity_id 
      LEFT JOIN topic_usage ON n.nid = u.entity_id 
      LEFT JOIN field_data_field_issue_reference r ON node.nid = f.entity_id , 
        ( select @min := ( 
            SELECT min(f.usage_value) from node n 
            LEFT JOIN field_data_field_topic_usage ON n.nid = f.entity_id 
           )
        ) var_init_subquery 
      AND node.status = 1 
      AND f.field_issue_reference_target_id = 708 
      order by f.usage_value, if(f.usage_value=@min, rand(), node.nid) 
      LIMIT 0,1

Solution 2 @Hitesh

    SELECT b.body, node.nid
    FROM node
    LEFT JOIN body ON node.nid = b.entity_id
    LEFT JOIN topic_usage ON node.nid = f.entity_id
    LEFT JOIN issue_reference f ON node.nid = f.entity_id
      AND (
        f.entity_type =  'node'
      AND
        f.deleted =  '0'
      )
    WHERE node.type = 'issue_paragraphs'
    AND node.status = 1
    AND f.field_issue_reference_target_id  = 708
    GROUP BY node.nid having f.usage_value=min(f.usage_value)
    ORDER BY rand() LIMIT 0,1

Upvotes: 1

Views: 67

Answers (3)

fancyPants
fancyPants

Reputation: 51868

Sample data:

CREATE TABLE t
    (`a` int, `b` int)
;

INSERT INTO t
    (`a`, `b`)
VALUES
    (1, 1),
    (2, 1),
    (3, 0),
    (4, 0),
    (5, 1),
    (6, 0)
;

Query:

select
*
from
t
order by b, if(b=0, rand(), a)

Every time you execute it, a column is ordered random for entries where b is 0 and ordered by a where b is not 0.

To have it working for your minimum, you could simply use variables:

select
a, b
from
t
, (select @min := (select min(b) from t)) var_init_subquery
order by b, if(b=@min, rand(), a)

Upvotes: 2

Hitesh Mundra
Hitesh Mundra

Reputation: 1588

I think you need to use Group by and having clause for selections and then use order by rand(). For example i am writing you query as following. My query may be wrong on based on column selections but i am tring to focus on approch.

 SELECT b.body, n.nid
      FROM node n
      LEFT JOIN body b ON n.nid = b.entity_id
      LEFT JOIN topic_usage u ON n.nid = u.entity_id
      LEFT JOIN reference r ON n.nid = r.entity_id
        AND (
          r.entity_type =  'node'
        AND
          r.deleted =  '0'
        )
      WHERE n.type = :type
      AND n.status = 1
      AND r.target_id= :id
      GROUP BY n.nid having u.field_topic_usage_value=min(u.field_topic_usage_value)
      ORDER BY rand()

Upvotes: 1

GergelyPolonkai
GergelyPolonkai

Reputation: 6421

As far as I know, this cannot be done with one query if you want to apply the RAND() ordering only for topics with 0 usage count. A possible workaround would be changing the ORDER BY clause like this:

ORDER BY u.field_topic_usage_value ASC, RAND()

This will apply random sorting to all topics with the same usage count as a possible workaround, while maintaining the ordering by usage count itself.

Upvotes: 0

Related Questions