Retrieve a random value from a predefined number list

Question

Setting:

I have a table of users that can contain 1 or N records inside, but for example let’s assume that I have 5 records there with their respective Ids, being them: 4, 23, 59, 17, 33

Is there any function or logic in SQL to randomly retrieve one of these predefined Ids?

I understand that could recover the Ids using the TOP and controlling whether or not that value has already been processed. But the idea is that it is even random.

I know there’s a function ROUND() but it is not possible to generate numbers from predefined values.

Answer 1

I managed using 3 functions CEILING RAND and CHOOSE

This way I can pass the amount of records I have in the table by randomly retrieving only the specific values it contains inside.

Example:

DECLARE @NUM INT = CEILING(RAND()*5)

SELECT CHOOSE (@NUM, 4,23,59,17,33)

See working in here

Answer 2

Sql has a function that returns a random number between a range (RAND()), puts to run a RAND() for each row of the table and sorts it by this column, searching also for the ID field and assigning to a variable. Return this variable to have the ID "randomly".

CREATE FUNCTION DBO.GET_ID_ALEATORIO()
RETURNS INT
AS
BEGIN
DECLARE @VAR INT
SELECT TOP 1
@VAR = ID,
RAND() AS ORDEM_ALEATORIA 
FROM TABELA
ORDER BY ORDEM_ALEATORIA
RETURN @VAR
END

SELECT DBO.GET_ID_ALEATORIO()