CODEWITHSUNDEEP
mysqldaterandom
ralph97
ralph97

Reputation: 33

Picking One Random Result From Each Date

I have a db structured like this:

int, date, varchar
1, 2021-07-25, aaaa
2, 2021-07-25, aaba
3, 2021-07-25, aabb
4, 2021-07-26, bbbb
5, 2021-07-26, bbba
6, 2021-07-26, bbaa
7, 2021-07-26, babb

I need to pull one result for each date at random. So one result for 07-25 and one result from 07-26 with this example.

Sample Output:

aaaa
bbbb

Upvotes: 0

Views: 69

Answers (3)

user16425306
user16425306

Reputation:

Here's one idea which, at least, satisfies the brief...

DROP TABLE IF EXISTS ralph97;

CREATE TABLE ralph97
(id INT AUTO_INCREMENT PRIMARY KEY
,date DATE NOT NULL
,my_string CHAR(4) NOT NULL
);

INSERT INTO ralph97 VALUES
(1,'2021-07-25','aaaa'),
(2,'2021-07-25','aaba'),
(3,'2021-07-25','aabb'),
(4,'2021-07-26','bbbb'),
(5,'2021-07-26','bbba'),
(6,'2021-07-26','bbaa'),
(7,'2021-07-26','babb');

...

WITH cte AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY date ORDER BY RAND()) x FROM ralph97)
SELECT id, date, my_string FROM cte WHERE x = 1;
+----+------------+-----------+
| id | date       | my_string |
+----+------------+-----------+
|  3 | 2021-07-25 | aabb      |
|  6 | 2021-07-26 | bbaa      |
+----+------------+-----------+

WITH cte AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY date ORDER BY RAND()) x FROM ralph97)
SELECT id, date, my_string FROM cte WHERE x = 1;
+----+------------+-----------+
| id | date       | my_string |
+----+------------+-----------+
|  2 | 2021-07-25 | aaba      |
|  4 | 2021-07-26 | bbbb      |
+----+------------+-----------+

Upvotes: 2

Ibrahim Abou Khalil
Ibrahim Abou Khalil

Reputation: 322

You can use the GROUP BY statement to help you achieve that, the query will be something like this:

SELECT varchar FROM {table-name} GROUP BY date;

Upvotes: 1

digijay
digijay

Reputation: 1366

You could also try

select varchar from table where date='2021-07-25'
order by rand() limit 1;

Upvotes: 2

Related Questions