SQL Server 2014 Random Value in Group By

Question

I'm trying to figure out how to get a single random row returned per account from a table. The table has multiple rows per account or in some cases just a single row. I want to be able to get a random result back in my select so each day that I run the same statement I might get a different result.

This is basis of the query:

select number, phonenumber
from phones_master with(nolock)
where phonetypeid = '3'

This is a sample result set

number         phonenumber    
--------------------------
4130772,       6789100949    
4130772,       6789257988
4130774,       6784519098
4130775,       6786006874

The column called Number is the account. I'd like to return a single random row. So based on the sample result set above the query should return 3 rows.

Any suggestions would be greatly appreciated. I'm beating my head against the wall with this one.

Thanks

Answer 1

You can use WITH TIES in concert with Row_Number()

Select Top 1 with ties *
 From  YourTable
 Order by Row_Number() over (Partition By Number Order By NewID())

Returns (for example)

number  phonenumber
4130772 6789257988
4130774 6784519098
4130775 6786006874