Randomizing query results

[LucL]

Here is what i'm trying to do using 1 SQL query (not sure if it's possible).

I want to display all objects matching a certain criteria (in this case a keyword) and then I want to display random results for the rest, BUT the data matching the criteria must be first, and then the random data.

This works, but doesn't have the random part...

SELECT * FROM content WHERE c_id <> 60 AND c_status="online" AND ((c_keywords="Corn Dog" OR c_keywords LIKE "%Corn%" OR c_keywords LIKE "%Dog%" ) OR length(c_keywords)>0) ORDER BY (case when c_keywords="Corn Dog" OR c_keywords LIKE "%Corn%" OR c_keywords LIKE "%Dog%" then 0 else 1 end) LIMIT 20

I tried adding a RAND() statement to the end but all I get is errors.

Thanks guys!
Luc L.

 

[mbrooks]

ORDER BY RAND() LIMIT 20

M. Brooks

http://mbrooks.info

 

[LucL]

M. Brooks,

That randomizes all results, but I need the ones that were exact matches to be first, and then the ones that were not-important ( .. OR LENGTH(c_keywords) > 0 ..) to be randomized after the important ones.

Luc L.

 

[r937]

try this --

select *
  from (
select * 
     , case when c_keywords = 'Corn Dog' 
              or c_keywords like '%Corn%' 
              or c_keywords like '%Dog%' 
            then 0 else 1 end
         as sortkey
  from content 
 where c_id <> 60 
   and c_status = 'online' 
   and (
       c_keywords = 'Corn Dog' 
    or c_keywords like '%Corn%' 
    or c_keywords like '%Dog%' 
    or length(c_keywords)>0
       ) 
order
    by rand() limit 20     
       ) as mydata
order
    by sortkey

r937.com | rudy.ca