Random Records with Criteria

Question

I would like some SQL command for a MS-Access database where I have 500 records and only to return 20 records so random being these (10 FEMININE and 10 MALE) and sorted in alphabetical order by name.

---

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

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

---

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

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 for your attention...

Answer 1

Working:

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:

Like you just want 10 FEM and 10 MASC, done 2 Subqueries draw them and limit them individually and then unite them with the UNION ALL.

In the external select, I used the same table to find the results of IDs and order them with the ORDER BY in the end, first by sexo, then by nome.

---

Auxiliary:

What’s the difference between UNION and UNION ALL?

---

---

Ancient answer:

(didn’t work)

Probably the Access does not accept table alias, 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