ORDER of STRCMP results

[mentalist]

Hi,

Here's a simplified version of my statement:

SELECT *, STRCMP(title,'word') as result FROM table ORDER BY result

 

The docs for STRCMP state:

mysql> SELECT STRCMP('text', 'text2');

        -> -1

mysql> SELECT STRCMP('text2', 'text');

        -> 1

mysql> SELECT STRCMP('text', 'text');

        -> 0

 

No matter if I use ASC or DESC, the results aren't really in order...

e.g.

ASC: -1, 0, 1

DESC: 1, 0, -1

 

I don't want to use a WHERE clause because I want all the results.

 

Any ideas?

 

 

Is there a way to dynamically reassign the results, e.g. 'if -1 then 2' ? ? ?

 

 

 

Cheers...

[J.Daniels]

STRCMP() will always return -1, 0, or 1 and it looks like you are comparing the value of title to 'word'.

 

STRCMP() is normally used for conditional statements.

 

If you want to order your result set by title, just use title in the ORDER BY clause.

 

SELECT * FROM table ORDER BY title

[mentalist]

This is just part of the set of clause and order bys.

 

It's a bit of a search thing and i'm after adding an extra weight if the title is an exact match. So a sort by 'title' would give nonsensical ordering. Even though, i'm not sure if any of it's results would be of any use other than if it were 0... hmmm

[mentalist]

Is there any way of doing maths in the ORDER section?

...
ORDER BY score_strcmp if = 0

[mentalist]

I seem to be able to go this way though...
[code
STRCMP(title, '".$searchy."') LIKE 0 as score_strcmp  

Which gives me either 0 or 1 results...

[J.Daniels]

I did a little searching and it seems you want to use the CASE statement:

 

SELECT *, (CASE STRCMP(title,'word') WHEN 0 THEN 0 ELSE 1 END) as result FROM table ORDER BY result

[mentalist]

oooh, I like the look of that, thanks...

 

Here's where it's at at the moment... but still just testing... seeing what weights what and goes where...

$s = "SELECT *, 
	MATCH(title,info) AGAINST ('".$searchy."') AS score, 
	LOCATE('".$searchy."', title) AS score_locate_title, 
	LOCATE('".$searchy."', info) AS score_locate_info, 
	(title REGEXP '".$searchy."') AS score_regex_title, 
	(info REGEXP '".$searchy."') AS score_regex_info,
	STRCMP(title, '".$searchy."') LIKE 0 AS score_strcmp  
	FROM test_search
	WHERE LOCATE('".$searchy."', title) || LOCATE('".$searchy."', info)

 

Cheers...