SQL Select random rows partitioned by a column-CodePudding

I have a dataset looks like this

| Country    | id |
-------------------
| a       | 5  |
| a       | 1  |
| a       | 2  |
| b       | 1  |
| b       | 5  |
| b       | 4  |
| b       | 7  |
| c       | 5  |
| c       | 1  |
| c       | 2  |

and i need a query which returns 2 random values from where country in ('a', 'c'):

| Country | id |
------------------
| a       | 2 |  -- Two random rows from Country = 'a'
| a       | 1 | 
| c       | 1 |
| c       | 5 | --Two random rows from Country = 'c'

CodePudding user response：

This should work:

select Country, id from
(select Country, 
id,
row_number() over(partition by Country order by rand()) as rn
from table_name
) t
where Country in ('a', 'c') and rn <= 2

Replace rand() with random() if you're using Postgres or newid() in SQL Server.