Group by random column in ms access
Multi tool use
Clash Royale CLAN TAG#URR8PPPGroup by random column in ms access
I need something like this in MS ACCESS SQL
SELECT
ID,
col1,
col2,
random(col3)
FROM
table
GROUP BY
ID,
col1,
col2
NOTE:
I want to remove duplicates choosing random value of col3.
INPUT:
+----+------+------+------+
| Id | col1 | col2 | col3 |
+----+------+------+------+
| 1 | A | B | 7 |
+----+------+------+------+
| 1 | A | B | 10 |
+----+------+------+------+
RESULT:
+----+------+------+------+
| Id | col1 | col2 | col3 |
+----+------+------+------+
| 1 | A | B | 7 |
+----+------+------+------+
REQUERY:
+----+------+------+------+
| Id | col1 | col2 | col3 |
+----+------+------+------+
| 1 | A | B | 10 |
+----+------+------+------+
I need remove duplicates but choose random row, id, col1, col2 are same but col3 not same
– Carlos Villegas Rojas
18 hours ago
As you use
random() like an aggregation function, I wonder if you want a random value from col3 in the group?– sticky bit
18 hours ago
random()
col3
I need something like an aggregation function but not MIN or MAX, I need random of col3
– Carlos Villegas Rojas
18 hours ago
Random function MAY return the same col3 values on subsequent requery iterations. Especially with above data where only two values of col3 exist. If REQUERY has a requirement to cycle thru each of the col3 values before repeating then we have a different problem.
– donPablo
17 hours ago
1 Answer
1
Give each row a random number, and let Row_Number() assign a value of 1 or 2 or 3 etc depending on the ordering of randoms. Finally select the columns you want where the RowNum is One.
select Id, col1, col2, col3
from
(Select *
, ROW_NUMBER() Over (Partition By ID, col1, col2
Order By RAND(CHECKSUM(NEWID())) ) as RowNumRandom
From col3Random
) as xx
Where RowNumRandom = 1
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
A random column from the table or a predefined column that grabs a random value?
– Edward
18 hours ago