Random Sort 4

[clapag22]

I have a provider directory on the web that returns results in alphabetical order by provider office name when you enter a zip code and a mileage radius. However, we are finding that the locations that show up on the top of the search (a, b, c, etc) are getting a lot more referrals than those on the bottom (s, t, u, etc). So instead of doing alpha order we would like them to come out in a random order everytime we do a search. I tried just adding a RAND() to the end of my query:

select f1, f2, f3, .., f12, rand()
from tb1, tb2
where etc etc

but when you do that it returns the same value for each row.

Any ideas?

Thanks!

 

[karluk]

I would try using rand() in an expression that includes the office name. It's likely that ordering by such an expression would be sufficiently random to satisfy your needs. As an example, consider

select office_name, cos(ascii(office_name)*rand()) from your_table
order by 2

This multiplies the random number by the ascii value of the first character of the office name, and then takes the cosine. Testing this out, it seems to work quite well at reordering the sort every time it is executed. Of course, I am assuming that it is ok to randomize by only the first character of the office name. You could also invent an expression that looked at multiple characters.

 

[clapag22]

Works great! Thanks!

 

[LikeThisName]

thanks for sharing, your valuable post helps me out 18 months after you post it.

LikeThisName <- ? Sorry It's Taken =)

 

[vongrunt]

This doesn't depend on any column and works under SQL2K:

select blah from someTable order by newid()

 

[LikeThisName]

apparently works in SQL Server 7 as well. thanks for the valuable post.

LikeThisName <- ? Sorry It's Taken =)

 

[TheBugSlayer]

SELECT F1 [,..Fn] FROM YourTable ORDER BY NEWID(). Your best bet.

 

[Meleagant]

Like LikeThisName said, another person greatly assisted by a post that is 14 months old. A star is born

Gonna get a good running start and throw myself at the wall as hard as I can man --WZ--