SQL query for multiple number of same art from a table

Question

I would love to ask if it is possible to write a mssql query, which will select records from the same group art in a table with a desired count randomly...

such as

id | name | surname | sex
1  | jack |  jones  | male
2  | jack |  david  | male
3  | john |  snow   | male
4  | emma | watson  | female
5  | linda| kruger  | female
6  | tim  |  burns  | male
7  | test |  test1  | female
8  | none | exist   | female
9  | test | test2   | nosex
10 | test | test3   | nosex
11 | test | test4   | nosex

From this list, I need two of each art randomly, namely 2 males, 2 females, and 2 nosex. How can i get that?

Answer 1

Use newid() to create a random order:

WITH cte as (
     SELECT *,
            ROW_NUMBER() OVER ( PARTITION BY sex 
                                ORDER BY newid()) as rn
     FROM YourTable
) 
SELECT *
FROM cte
WHERE rn <=2