CODEWITHSUNDEEP
javasql-server
Tony
Tony

Reputation: 1165

Randomizing select distinct

We use multiple databases at work, at least Oracle and MSSQL. Not sure why we have to use both but this question is about MSSQL.

There is a table, let's just call it System..DirectMapping that has a field accountID. I can't guarantee they are all numeric, but they appear to be.

So really my question is in two related parts.

First, I have to find a valid account id. That is one that is in the table. This is straight forward, but I would like to get a random one.

So I did a 

select distinct accountID from System..DirectMapping

which works but they are always in the same order. I read how you could randomize something using newid() so I tried 

 select distinct accountID from System..DirectMapping order by newid()

but it gave me an error that when selecting distinct, the order by field must occur in the select which would not make sense here. So I tried

 select accountID from
 (select distinct accountID from System..DirectMapping) j
 order by newid()

but it gave me a similar error about not being able to order by in views. I finally just read the whole thing into a java array and did a Collections.shuffle() but it would be better to do it from the query because then I could limit the number of results (I think like top 10..). So is there any way to do that?

I will save the second question for later.

Upvotes: 4

Views: 75

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

You can do an ORDER BY in a view if you use a TOP specification:

 select TOP 100 PERCENT accountID from
 (select distinct accountID from System..DirectMapping) j
 order by newid()

Upvotes: 3

Related Questions