You'd have to write
SELECT CompanyTbl.*
FROM CompanyTbl
WHERE CompanyWantsMiniSite = 'Yes' AND MakeMiniSitePublic = 'Yes'
ORDER BY CompanyTbl.ClassOfService, rnd()
Some dbms's will happily generate the same random number for each row. I
know this works in SQL Server:
SELECT *
FROM myTable
ORDER BY NEWID()
> -----Original Message-----
> From: Tom Forbes [mailto:[EMAIL PROTECTED]
> Sent: Friday, 4 April 2003 10:45 a.m.
> To: CF-Talk
> Subject: RE: How can I "ORDER BY RANDOM" ?
>
> Hi Russ,
>
> I did that, but I get an error stating that there is no column in the db
> named SortOrder. Here is the modified query:
>
> <CFQUERY NAME="GetAllCompaniesWithMiniSites" DATASOURCE="MedMallDs"
> DBTYPE="ODBC">
>
> SELECT CompanyTbl.*, Rnd() as SortOrder
>
> FROM CompanyTbl
>
> WHERE CompanyWantsMiniSite = 'Yes' AND MakeMiniSitePublic = 'Yes'
>
> ORDER BY CompanyTbl.ClassOfService, SortOrder
>
> </CFQUERY>
>
> Thanks,
>
> Tom
>
> At 04:10 PM 4/3/03 -0600, you wrote:
> >If your database has a random number generator function, you can try:
> >
> >SELECT tblCompany.*, Random() as SortOrder FROM tblCompany ORDER by
> >SortOrder
> >
> >HTH--haven't tried it myself.
> >
> >Russ
> >
> > > -----Original Message-----
> > > From: Tom Forbes [mailto:[EMAIL PROTECTED]
> > > Sent: Thursday, April 03, 2003 4:06 PM
> > > To: CF-Talk
> > > Subject: How can I "ORDER BY RANDOM" ?
> > >
> > >
> > > Dear CF'ers:
> > >
> > > I have searched my CF books for a clue, but am unable to find
> > > an example of
> > > what I am trying to do.
> > >
> > > I need to modify the below query so that my results are still
> > > ordered by
> > > "ClassOfService" (a number between 1 and 4) - BUT, the
> > > "CompanyName" needs
> > > to be displayed in a RANDOM ORDER each time the query is run,
> > > rather than
> > > its current alphabetical order. This will give all companies that
> > > participate an even advantage of being selected rather than
> > > those that
> > > begin with the first letters of the alphabet.
> > >
> > > Can someone please point me in the right direction? I have
> > > studied the CF
> > > RANDOM tag but can't figure out how to apply it.
> > >
> > > <CFQUERY NAME="GetAllCompaniesWithMiniSites" DATASOURCE="MedMallDs"
> > > DBTYPE="ODBC">
> > >
> > > SELECT *
> > >
> > > FROM CompanyTbl
> > >
> > > WHERE CompanyWantsMiniSite = 'Yes' AND
> > > MakeMiniSitePublic = 'Yes'
> > >
> > > ORDER BY CompanyTbl.ClassOfService, CompanyTbl.CompanyName
> > >
> > > </CFQUERY>
> > >
> > > Thanks very much!!
> > >
> > > Tom Forbes
> > >
> > >
> > >
> > > **************************************************************
> > > *********
> > > Tom Forbes, Forbes Consulting, Inc.
> > > TEL: 407-772-3427 FAX: 407-772-3428
> > > Visit www.medmatrix.com for Medical Equipment Web Sites!
> > > E-Mail Subscription at www.medmatrix.com/mail_list.htm
> > > **************************************************************
> > > *********
> > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
