Random Records with Criteria

Question

Random Records with Criteria

Navigation

#1 by (2 votes)

3

I would like some SQL command for an MS-Access database where I have 500 records and only 20 records to return random being these (10 in 10 MEN) and sorted alphabetically by name.

The structure of the tbLista table is very simple ...

    ID - Long (500 registros)
    NOME - Texto
    SEXO - Texto (FEMININO/MASCULINO)

This code is what I have, but only returns the records at random ...

SELECT TOP 20 tbNew.ID, tbNew.NOME, tbNew.SEXO
FROM (
    SELECT tbLista.ID, tbLista.NOME, tbLista.SEXO 
    FROM tbLista 
    ORDER BY Rnd(Len(ID))
) AS tbNew;

Thank you all for the attention...

sql ms-access

asked by anonymous 20.08.2018 / 17:31

1 answer

How do I use Google's reCAPTCHA to block a particular part of a page? TextBox mascara value from 2 to 4 decimal places in WPF C #

score 2 · Accepted Answer

Running:

SELECT tbNew.ID, tbNew.NOME, tbNew.SEXO
FROM tbLista tbNew
WHERE tbNew.ID IN (
    (SELECT TOP 10 tbLista.ID 
        FROM tbLista
        WHERE tbLista.SEXO = 'FEMININO'
        ORDER BY Rnd(Len(ID)))
    UNION ALL
    (SELECT TOP 10 tbLista.ID 
        FROM tbLista
        WHERE tbLista.SEXO = 'MASCULINO'
        ORDER BY Rnd(Len(ID)))
)
ORDER BY tbNew.SEXO, tbNew.NOME

Explaining:

As you just want 10 FEM and 10 MASC , I made 2 subqueries by sorting and limiting them individually and then merging them with UNION ALL .

In the external select, I used the same table to fetch the results of IDs of the subqueries, and sort them with ORDER BY at the end, first by sexo , then nome .

Auxiliary:

Which is the difference between UNION and UNION ALL?

Old answer:

(did not work)

Access does not accept aliases for the table, so it did not work the first way I passed:

SELECT tbNew.ID, tbNew.NOME, tbNew.SEXO
FROM (
    (SELECT TOP 10 tbLista.ID, tbLista.NOME, tbLista.SEXO 
        FROM tbLista
        WHERE tbLista.SEXO = 'FEMININO'
        ORDER BY Rnd(Len(ID)))
    UNION ALL
    (SELECT TOP 10 tbLista.ID, tbLista.NOME, tbLista.SEXO 
        FROM tbLista
        WHERE tbLista.SEXO = 'MASCULINO'
        ORDER BY Rnd(Len(ID)))
) AS tbNew
ORDER BY tbNew.SEXO, tbNew.NOME