André van Rensburg André van Rensburg - 1 month ago 18
SQL Question

Need to run a query on a Transactions table with multiple entries for multiple users

The table has columns for id, user_id, payer_id, amount, status, date etc.
Multiple users have multiple transactions...

I need to query 1 (RANDOM) transaction per user, for ALL users.

So if there are 100 users, and 10000 transactions, the result must give me 100 transactions in total as there are 100 users, irrespective of how many transactions each user has.

I have read that DISTINCT and GROUP BY must be used but I have not found a way for this to work.

The problem may be with the random part, any advise?

Answer

Try this

select * from (select * from table order by rand())as random group by random.user_id;