Question 276345 in Mysql Question posted in
The official MySQL documentation can be found here.

Extract two random days from month and year using MySQL 8

I have upgraded MySQL database to 8.1 release

But I can’t extract two random days from month and year from my MySQL table

On this table, I have stored all days from each month of the year and for each row it’s stored the login of users

I need random extracting, e.g.

All rows of the day 2023-10-29 and all rows of the day 2023-10-03 or all rows of the day 2023-10-18 and all rows of the day 2023-10-14, excluding all other days of the month.

I have tried this query, but on the return I have all days and all rows of October month from the table _tbl_login…

I need only two days from the table _tbl_login of october month

WITH rando AS 
( SELECT ` _Date`, 
  ROW_NUMBER() 
  OVER ( PARTITION BY ` _Date` ORDER BY RAND() ) AS rn 
  FROM _tbl_login WHERE MONTH ( _Date ) = '10' ) 
SELECT * FROM
    _tbl_login 
WHERE
    ` _Date` IN ( SELECT ` _Date` FROM rando WHERE rn <= 2 ) 
    AND MONTH ( _Date ) = '10';

Edit #1

WITH rando AS 
( SELECT _Date, 
  ROW_NUMBER()  
    OVER ( ORDER BY RAND() ) AS rn 
  FROM _tbl_login WHERE MONTH ( _Date ) = '10' ) 
SELECT _Date FROM
    _tbl_login 
WHERE
    _Date IN ( SELECT _Date FROM rando WHERE rn <= 2 ) 
    AND MONTH (_Date ) = '10';
+------------+
| _Date      |
+------------+
| 2023-10-02 |
| 2023-10-06 |
+------------+
2 rows in set (1.75 sec)

I need this

+-----+------------+------------+
| -id | _Date      | _login     |
+-----+------------+------------+
|  1  | 2023-10-07 | Kevin      |
|  2  | 2023-10-07 | Joe        |
|  3  | 2023-10-07 | Lydia      |
|  4  | 2023-10-07 | Homer      |
|  5  | 2023-10-07 | Ada        |
|  6  | 2023-10-07 | Lucy       |
|  7  | 2023-10-07 | Kyara      |
|  8  | 2023-10-07 | Lucas      |
|  9  | 2023-10-07 | Steve      |
|  10 | 2023-10-24 | Steve      |
|  11 | 2023-10-24 | Ada        |
|  12 | 2023-10-24 | Frank      |
|  13 | 2023-10-24 | Clint      |
|  14 | 2023-10-24 | Kyara      |
|  15 | 2023-10-24 | Maddy      |
|  16 | 2023-10-24 | Peter      |
|  17 | 2023-10-24 | Lucas      |
+-----+------------+------------+

And not this

+------------+
| _Date      |
+------------+
| 2023-10-07 |
| 2023-10-24 |
+------------+

2 rows in set (1.75 sec)

Tags:

2

Answers


  1. 0

    Remove PARTITION BY _Date from your window function.

    What that does is start the row numbering over at 1 for each date. That is, every respective date will have a row numbered 1.

    Login or Signup to reply.
  2. 0

    Try this on DB Fiddle

    WITH dates AS
     ( SELECT DISTINCT 
              _Date
         FROM _tbl_login 
        WHERE MONTH ( _Date ) = '10' ) )
   , rando AS
     ( SELECT *     
            , ROW_NUMBER() 
                OVER(ORDER BY RAND()) AS rn 
         FROM dates )   
SELECT *
  FROM _tbl_login 
 WHERE _Date IN
       ( SELECT _Date
           FROM rando  
          WHERE rn <= 2 ) 
 AND MONTH ( _Date ) = '10' );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search