Villanite Villanite - 4 months ago 11
SQL Question

Selecting Random Rows in relation to Random Rows

So I've been trying for a few days now and looking all over but I can't seem to find a solution for this situation. Maybe I'm over-thinking it, so I guess this is two questions in one:


  1. Am I even going the right direction?

  2. Why isn't this working as expected?



Situation:

I have a website where users can sign up, creating a "User". After registration, they can then create multiple profiles or "Characters".

I need a way to select a particular number of random users (in this case 12) and then select one of their characters at random.

Current Data Structure:

Users Table - {UserID}

Characters Table - {CharacterID, DisplayName, UserID}

Problem:

Usually I do this kind of stuff via code, but I wanted to approach this in a purely SQL method primarily because I didn't want to hit the code once for the random list of users and then 12 more times for each random character per user.

Eventually I came to the conclusion that this wasn't something that could be done with a single one-line query (if I'm wrong please correct me, I may just not be seeing the trees in the forest here). So I decided to select the 12 random users, loop through them and on each one select a random character for each user.

This seems to work and from what I can tell, it's not horrendous in terms of performance. However... I'm running into a small problem with the returned data. It only returns 12 rows sometimes. Other times it jumps down to 11 rows or 10 rows and I can't for the life of me figure out why it's doing this. Would anyone be able to shed light on this?

Code:

Declare @UserTable TABLE(UserID int)
Insert Into @UserTable Select Top 12 UserID From Users Where ((ABS(CAST( (BINARY_CHECKSUM(*) * RAND()) as int)) % 100) < 10)

Declare @OutputTable TABLE(CharacterID int, CharacterDisplayName nvarchar(MAX), UserID int)

Declare @CurrentUserID int
Select @CurrentUserID = min(UserID) From @UserTable
While @CurrentUserID is not null
Begin
Insert Into @OutputTable Select Top 1 CharacterID, CharacterDisplayName, UserID FROM CharactersForListing Where UserID = @CurrentUserID Order By NewID()
Select @CurrentUserID = min(UserID) from @UserTable Where UserID > @CurrentUserID
End

Select * From @OutputTable

Answer

How about something like this? The main query gets 12 random Users. The correlated cross apply gets 1 randomly selected Character for that User.

select top 12
    u.UserID
    , c.CharacterID
from Users u
cross apply
(
    select top 1 CharacterID
    from Characters ch
    where ch.UserID = u.UserID
    order by newid()
) c
order by NEWID()