Select randoms records from table but not more than 2 records of same name

Question

I want to fetch TOP N random records from the table but not more than 2 records for same name.

SELECT TOP 7 Table1.ID, Table1.Name, Table1.Salary, Rnd(Abs([Table1]![id])) AS Expr1
FROM Table1
GROUP BY Table1.ID, Table1.Name, Table1.Salary, Rnd(Abs([Table1]![id]))
ORDER BY Rnd(Abs([Table1]![id]));

It is giving more than two records for same name. Would someone please provide some assistance.

This doesn't look like T-SQL. `!` is not an object delimiter and `RND` isn't a function in the language. Are you using SQL Server or (MS?) Access here? Please update your tags appropriately — Thom A, Oct 22 '19 at 19:17
Possible [duplicate](https://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database)... — Peter Schneider, Oct 22 '19 at 19:49

Gustav · Answer 1 · 2019-10-23T10:22:34.080

Use this query:

SELECT 
    ID, 
    [Name]
FROM 
    [Table1]
ORDER BY 
Rnd(-Timer()*[ID]);

Then open it as a Recordset and traverse it from the start and pick IDs (could be saved in an array) while recording the the Name used (a Collection could be used for this).

If a Name has been used twice, skip the record and move to the next.

When you have picked seven IDs, stop. The array of IDs will identify your seven records.

Save the query as RandomAll. Then use it in this function:

Public Function RandomTwo() As long()

    Dim rs      As DAO.Recordset
    Dim Names   As New Collection
    Dim Used    As Integer
    Dim Index   As Integer

    Dim Ids()   As Long

    Set rs = CurrentDb.OpenRecordset("RandomAll")

    ReDim Ids(0)
    Do While Not rs.EOF
        Used = 0

        ' Read used count. Will fail if not used.
        On Error Resume Next
        Used = Val(Names.Item(rs.Fields(1).Value))
        On Error GoTo 0
        Debug.Print Used, ;

        If Used = 1 Then
            ' Remove key to be added later with updated use count.
            Names.Remove rs.Fields(1).Value
        End If
        If Used < 2 Then
            ' Record the use count (as text) of the key.
            Names.Add CStr(Used + 1), rs.Fields(1).Value
            Debug.Print rs!ID.Value, rs.Fields(1).Value
            ' Add ID to array.
            Ids(UBound(Ids)) = rs!ID.Value
            If UBound(Ids) = 6 Then
                ' Seven IDs found.
                Exit Do
            Else
                ' Prepare for next ID.
                ReDim Preserve Ids(UBound(Ids) + 1)
            End If
        End If
        rs.MoveNext
    Loop
    rs.Close

    ' List the found IDs.
    For Index = LBound(Ids) To UBound(Ids)
        Debug.Print Index, Ids(Index)
    Next

    ' Return the IDs.
    RandomTwo = Ids

End Function

The function will return the array holding the seven IDs.

Thanks for code Gustav, it is giving random selection but still I am getting more than 2 records for same name...how can I use array and collection in Access..could you please help me out — Umesh Yadav, Oct 23 '19 at 09:25

Taazar · Answer 2 · 2019-10-23T10:56:46.220

Taking inspiration from Gustav's answer I have designed a bit of VBA code that will generate a SQL string which when used will give you N amount of random records with a limit of 2 per name.

Const PicksLimit As Long = 7 'How many records do you want to select

Dim rs As DAO.Recordset
'Select randomised table
Set rs = CurrentDb.OpenRecordset("SELECT ID, Name From Table1 ORDER BY Rnd(Abs(ID))")

'Define variables for keeping track of picked IDs
Dim Picks As Long, PickNames As String, PicksSQL As String
Picks = 0
PickNames = ""
PicksSQL = ""

With rs
    If Not (.BOF And .EOF) Then 'If table is not empty...
        .MoveFirst
        'Loop until limit reached or table fully looked through
        Do Until Picks = PicksLimit Or .EOF
            'If name has been picked less than twice before
            If Len(PickNames) - Len(Replace(PickNames, "[" & !Name & "]", "")) < ((Len(!Name) + 2) * 2) Then
                Picks = Picks + 1 'Increment counter
                PickNames = PickNames & "[" & !Name & "]" 'Add name for later checks
                PicksSQL = PicksSQL & "ID = " & !Id & " OR " 'Append SQL string
            End If
            .MoveNext
        Loop
        'Add front sql section and remove last OR
        PicksSQL = "SELECT * FROM Table1 WHERE " & Left(PicksSQL, Len(PicksSQL) - 4)
    Else
        'If the table is empty no need for ID checks
        PicksSQL = "SELECT * FROM Table1"
    End If
End With

rs.Close
Set rs = Nothing

'Print SQL String (This can be changed to set a RecordSource or similar
Debug.Print (PicksSQL)

At the moment the SQL string is just printed to the Immediate window but this can be changed to go wherever you need, like a subform's RecordSource for instance.

The code will need to be run every time you want a new random list but it shouldn't take a huge amount of time so I don't see that being too big an issue.

If you need help integrating the code into whatever it is you're doing just let me know. It was actually sorta fun to try figure this out — Taazar, Oct 23 '19 at 10:26
Hi Taazar, its giving select * from table1 where id = 1 or Id = 2 etc also what if I have multiple columns but name shoud not more than 2 — Umesh Yadav, Oct 23 '19 at 11:16
Umesh Yadav, Multiple columns won't affect it. It selects all the columns in the table to show, the reason ID is the only column named is because it is the only column that needs a filter. — Taazar, Oct 23 '19 at 11:19

Select randoms records from table but not more than 2 records of same name

2 Answers2