Random Record

[redoakhg]

Hi,

The issue: I need to pull 1 random record form a table to send a message via email to a recipient.

Client can select times and message types.

Once a message has been sent it is flagged and won't be sent again to the client (until all messages within a given category have been sent to the client and then the system reloads the list).

I don't want to sort by message id for several reasons. However, when I run the following query, the recipients receive messages in Alphabetical order.

<cfquery name="NAME" datasource="#clientdsn#" maxrows="1">
Select message,messageid...
From TABLE
Where Messagetypeid = '#checkemails.messagetype#' AND Not Exists
(Select 1 from clienttomessagesent
Where clienttomessagesent.messageid = messages.messageid and clienttomessagesent.clientid = '#checkemails.clientid#')
ORDER BY RAND()
</cfquery>

How do I simply pull a random message from the list?

Driving me nuts.

 

[intelwizrd]

Are you trying to send one at a time? What is the database that you are using? You could just pull all of the records with the query and then loop through the query one by one to send the message and add the record to know it was sent. If you just wanted to do it one at a time, you could do this

<cfquery name="NAME" datasource="#clientdsn#" maxrows="1">
     SELECT TOP 1 message,messageid...
     FROM TABLE
     WHERE Messagetypeid = '#checkemails.messagetype#' AND NOT EXISTS
          (SELECT 1 FROM clienttomessagesent
           WHERE clienttomessagesent.messageid = messages.messageid 
           AND clienttomessagesent.clientid = '#checkemails.clientid#')
     ORDER BY RAND()
</cfquery>

Any reason you are not joining the tables together? Might help the query perform better.