order by find_in_set(), and_then, and_then

Question

This mysql query doesn't seem to work for me:

select *
from myTable
order by
  find_in_set( category, "First, Second" ),
  sortby,
  title;

Apparently you can't order beyond the find_in_set function?

Is there a way around this? Am I missing something?

Thanks for your reply.

Edit:

This is the exact query being run:

select
  uid,
  category,
  title,
  summary,
  image_url,
  link_url,
  link_text
from links_and_documents
where link_url != ''
  and status=1
order by
  find_in_set( category, "Test_Category_Two,Test_Category_One" ),
  sortby,
  title;

It returns all the items you would expect but orders them by sortby and completely ignores the find_in_set function.

If I leave ,sortby,title off the end of the query it sorts items by the find_in_set function.:

select
  uid,
  category,
  title,
  summary,
  image_url,
  link_url,
  link_text
from links_and_documents
where link_url != ''
  and status=1
order by
  find_in_set( category, "Test_Category_Two,Test_Category_One" );

Thanks for taking a look.

Table Structure:

CREATE TABLE `links_and_documents` (
  `id` int(11) NOT NULL auto_increment,
  `dateadded` varchar(19) NOT NULL default '',
  `dateupdated` varchar(19) NOT NULL default '',
  `uid` varchar(16) NOT NULL default '',
  `clientuid` varchar(16) NOT NULL default '',
  `status` char(1) NOT NULL default '1',
  `sortby` varchar(16) NOT NULL default '',
  `category` text NOT NULL,
  `title` text NOT NULL,
  `information` text NOT NULL,
  `summary` text NOT NULL,
  `services` text NOT NULL,
  `link_url` text NOT NULL,
  `link_text` text NOT NULL,
  `document_url` text NOT NULL,
  `document_text` text NOT NULL,
  `image_url` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `uid` (`uid`),
  KEY `category` (`status`,`category`(30))
)

I receive no errors.

Answer 1

I was running into the same problem from several months:

order by find_in_set( category, "First, Second" )

use to work sometimes and sometimes NOT. Today I read a lot and the next change solved my problem:

ORDER BY FIELD( category, 'First', 'Second' )

Hope it's not too late to help you.

Answer 2

You should not keep spaces in

order by find_in_set( category, "First, Second, Third, Fourth" )

should be

order by find_in_set(category,"First,Second,Third,Fourth" )