Extract two random days from month and year using MySQL 8

UncleVince · October 30, 2023, 2:58pm

Hi all,
I need your help.

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

Can you help me?
Thks

r937 · October 30, 2023, 3:02pm

ORDER BY RAND() LIMIT 2

RAND()

UncleVince · October 30, 2023, 3:12pm

Thanks

My query

SELECT
	_Date 
FROM
	`_tbl_login` 
ORDER BY
	RAND() 
	LIMIT 2;
+------------+
| _Date      |
+------------+
| 2023-10-30 |
| 2023-10-10 |
+------------+

2 rows in set (0.29 sec)

But in return only two rows of _Date…

If try

SELECT
	COUNT(*) _q,
	_Date 
FROM
	`_tbl_login` 
GROUP BY
	_Date 
ORDER BY
	RAND() 
	LIMIT 2;
+----+------------+
| _q | _Date      |
+----+------------+
|  9 | 2023-09-07 |
|  8 | 2023-10-24 |
+----+------------+

I need the details of the 9 rows stored in _Date 2023-09-07 and the details of 8 rows stored in _Date 2023-09-24

r937 · October 30, 2023, 3:35pm

“i need the details” suggests all columns, so i guess using the asterisk is okay

SELECT *
  FROM _tbl_login
 WHERE _Date IN
       ( SELECT _Date
           FROM _tbl_login
         GROUP
             BY _Date
        ORDER
            BY RAND() LIMIT 2 )

UncleVince · October 30, 2023, 4:04pm

Thanks

But

SELECT *
  FROM _tbl_login
 WHERE _Date IN
       ( SELECT _Date
           FROM _tbl_login
         GROUP
             BY _Date
        ORDER
            BY RAND() LIMIT 2 );
> 1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
> Time: 0,078s

The version of MYSQL is 8.0.12

r937 · October 30, 2023, 4:48pm

okay, try this

WITH rando AS
     ( SELECT _Date
            , ROW_NUMBER()
                OVER(ORDER BY RAND() ) AS rn 
         FROM _tbl_login )
SELECT *
  FROM _tbl_login
 WHERE _Date IN 
       ( SELECT _Date
           FROM rando 
          WHERE rn <= 2 )

UncleVince · October 30, 2023, 4:54pm

r937:

WITH rando AS
     ( SELECT _Date
            , ROW_NUMBER()
                OVER(PARTITION BY _Date
                         ORDER BY RAND() ) AS rn 
         FROM _tbl_login )
SELECT *
  FROM _tbl_login
 WHERE _Date IN 
       ( SELECT _Date
           FROM rando 
          WHERE rn <= 2 )

Okay, thanks

The last query extract all days and all rows from the table _tbl_login

r937 · October 30, 2023, 5:18pm

yikes

UncleVince · October 30, 2023, 5:25pm

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';

r937 · October 30, 2023, 5:34pm

WITH dates AS
     ( SELECT DISTINCT 
              _Date
         FROM _tbl_login 
        WHERE _Date >= '2023-10-01'
          AND _Date  < '2023-11-01' )
   , rando AS
     ( SELECT _Date     
            , ROW_NUMBER() 
                OVER(ORDER BY RAND()) AS rn 
         FROM dates )   
SELECT * 
  FROM rando  
 WHERE rn <= 2

r937 · October 30, 2023, 5:36pm

WITH dates AS
     ( SELECT DISTINCT 
              _Date
         FROM _tbl_login 
        WHERE _Date >= '2023-10-01'
          AND _Date  < '2023-11-01' )
   , rando AS
     ( SELECT _Date     
            , ROW_NUMBER() 
                OVER(ORDER BY RAND()) AS rn 
         FROM dates )   
SELECT * 
  FROM _tbl_login 
 WHERE _Date IN
       ( SELECT _Date
           FROM rando  
          WHERE rn <= 2 )

UncleVince · October 30, 2023, 5:41pm

Thanks, but the output is only two rows…

mysql> WITH dates AS
     ( SELECT DISTINCT 
              _Date
         FROM _tbl_login 
        WHERE _Date >= '2023-10-01'
          AND _Date  < '2023-11-01' )
   , rando AS
     ( SELECT _Date     
            , ROW_NUMBER() 
                OVER(ORDER BY RAND()) AS rn 
         FROM dates )   
SELECT _Date
  FROM _tbl_login 
 WHERE _Date IN
       ( SELECT _Date
           FROM rando  
          WHERE rn <= 2 );
+-------------------------+
| _Date                   |
+-------------------------+
| 2023-10-25              |
| 2023-10-16              |
+-------------------------+
2 rows in set (0.34 sec)

r937 · October 30, 2023, 7:28pm

aaaaargh!! my bad

instead of SELECT _Date write SELECT *

UncleVince · October 31, 2023, 7:07am

Okay, thanks

No problem.

The latest query continues to return only two rows.

I need to have two random days complete with all the rows of those two days… Maybe I did not say it clear enough…

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)

r937 · October 31, 2023, 2:22pm

okay, considering my batting average on this one, i am going to stop writing wild queries and ask that you prepare a fiddle so that i can test my queries against your data

feel free to use SQL Fiddle or DB Fiddle, and it won’t matter which dbms you choose, as i believe they all support CTEs, which is all we need here

let me know when it’s ready

UncleVince · October 31, 2023, 3:24pm

Thank you for your availability and kindness. Really appreciated

I think there must be a problem on my Hosting MySql 8 database version.

Tested on https://www.db-fiddle.com/f/7862PaTbkaTqvxd4jMoFEE/0 your query works correctly and I have two random days complete with all the rows of those two days…

The same query tried in MySql hosting continues to return only two rows… maybe a problem in the upgrade from 5 to 8 version… I don’t know…

I will ask the service Provider for information…

Thanks again, mate!

r937 · October 31, 2023, 4:17pm

that was a really good fiddle, well done!!

perhaps your hosted data doesn’t inlcude all the rows you think it does

what do you get for this –


SELECT _Date
     , COUNT(*)
  FROM _tbl_login
GROUP
    BY _Date

UncleVince · October 31, 2023, 4:48pm

Get this on hosting and on the DB Fiddle

It’s always confusing with these hostings…

mysql> SELECT _Date
     , COUNT(*)
  FROM _tbl_login
GROUP
    BY _Date with rollup;
+------------+----------+
| _Date      | COUNT(*) |
+------------+----------+
| 2023-10-07 |        9 |
| 2023-10-08 |        5 |
| 2023-10-21 |        8 |
| 2023-10-24 |        8 |
| NULL       |       30 |
+------------+----------+
5 rows in set (0.03 sec)

system · January 30, 2024, 11:49pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.