Skip to main content
A
ACS LLC
Inspiring
December 16, 2011
Answered

Retrieving random record set from SQL and also applying an ORDER

  • December 16, 2011
  • 2 replies
  • 5718 views

I need a query that is capable of obtaining X amount of random records from a SQL2008 database, but I also want those results to then order by a numberic field in the same table, camp_priority

I have

SELECT TOP #DisplayAmount#,camp_uid,camp_title,camp_text,camp_image_type
FROM campaigns
WHERE camp_uid IN

etc..etc

then at the bottom I have

ORDER BY newid(),camp_priority

The problem seems to be that I'm getting random records, but it's not ordering by the camp_priority

Anybody have any ideas?

Thanks

Mark

This topic has been closed for replies.
Correct answer ACS LLC

Your first subquery doesn't have a SELECT statement.  That'll be one source of errors.

As for weighting the significance of your records before returning them "randomly", you could order them on the sum or product of two components:  a weighting and a random part.  A very simple (perhaps overly simple) would be to weight the records between 1-5, and then add a random component between 1-5 (or 1-10, whatever... you'll need to decide the significance of each).

I can't comment on whether newid() is slow or not... have you got a citation for that (out of interest)?

--

Adam


Hey Adam,

I misread your original post, now I see how it was supposed to work, just posted below... got it working ... works great. hopefully it's not killing the CPU as this query will be use a LOT.. I'll just get a bigger CPU ;-)

With regards to the NEWID() performance issue, I don't have a specific link, I was just hunting around different forums etc and found a few posts that had talked about performance issues, and one that had a command I'd never seen that took a percentage of records with a lot of perfomance test graphs showing it was 10 times slow than the other approach.

I'm just going to have to see how it goes

Thanks for the help

Mark

2 replies

A
Adam Cameron.
Inspiring
December 17, 2011

I'd wanna check the performance of it before running with it as a solution, but this [sort of thing] should work:

SELECT *

FROM (

     -- your actual query here

     ORDER BY NEWID()

) subq

ORDER BY subq.columnYouWantToOrderBy

--

Adam

A
ACS LLCAuthor
Inspiring
December 17, 2011

It looks like I must have the syntax wrong, it's throwing an error.

I've posted the query lower down.

I had to pause for some thought after posting this thread, because the logic that I'm trying to apply to this is probably not going to serve it's purpose. On one hand I want to draw random records from the campaigns table, let's say that's 6 random records that meet the required criteria of correct country, category etc. On the other hand I also want to use my camp_priority to push campaigns up the list.

The issue is - Let's suppose I have 100 campaigns in there with mixed priority levels from 1 to 10, 1 being the highest, and I randomly pick 6 of them and then order them by priority, it won't be doing much justice to the priority set up because it could technically randomly pick out 6 x priority=10. and some 9's... If I have it so it picks 6 random ones of the highest priority then that also causes an issue because you'd never get to see the lower priority campaigns.

I think the best way to handle this is to set all camp_priority to 5 so they are all level, and then anything we want to push up we then set to a higher level 1 to 4, so if we just set 2 campaigns to priority level 1 and 2 then these two would take up the first two slots due to their higher priority and the next 4 slots would be random as they are all same priority, after yapping on so much about this, I think your suggestion would work, if I could get it to function

btw I read that NEWID() has rather slow performance, but finding another solution looked way to complicated for me.

<CFQUERY name="GetCamp" DATASOURCE="#datasource#">

    SELECT TOP #DisplayAmount# camp_uid,camp_title,camp_text,camp_image_type

    FROM (campaigns

    WHERE camp_uid IN

    <!--- MATCH COUNTRY --->

(SELECT camptarget_camp_uid FROM CampTarget

WHERE camptarget_country = '#session.usercountry#')

    AND camp_UID IN

    <!--- ALL CATEGORIES THAT THE CAMPAIGN IS IN --->

(SELECT category_camp_UID FROM category_list

WHERE category_cat_uid IN

    <!--- BASED ON CATEGORIES REQUIRED FOR THIS TRACKING ACCOUNT UID --->

(SELECT aff_cat_uid FROM aff_cat

WHERE aff_cat_aff_sub_uid = #val(session.affsub)#)

)

AND camp_status = 1 AND camp_stack = 1

<CFIF #ListLen(session.servedcamps)# GT "0">

AND camp_uid NOT IN (#session.ServedCamps#)

</CFIF>

ORDER BY newid()

)

subq ORDER BY subq.camp_priority

</CFQUERY>

A
Adam Cameron.
Inspiring
December 18, 2011

Your first subquery doesn't have a SELECT statement.  That'll be one source of errors.

As for weighting the significance of your records before returning them "randomly", you could order them on the sum or product of two components:  a weighting and a random part.  A very simple (perhaps overly simple) would be to weight the records between 1-5, and then add a random component between 1-5 (or 1-10, whatever... you'll need to decide the significance of each).

I can't comment on whether newid() is slow or not... have you got a citation for that (out of interest)?

--

Adam

D
Dan_Bracuk
Inspiring
December 16, 2011

I have an idea.  Take the newid() out of the order by clause.

Mind you, that only solves the order by problem.  I don't share your opinion that you are getting random records.  You'll have to give that one some more thought.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices