469,590 Members | 1,949 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,590 developers. It's quick & easy.

order by difference - possible?

Hi,

I'm trying to do a MySQL Query using Mysql 3.23.58 something like that below

SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");

The difference piece doesn't seem to be working (Syntax Error returned). Is
this not possible without upgrading the server? or am I doing something
wrong, all I want to do is be able to return the results closest to that
entered first.

Thanks in advance

John
Jul 20 '05 #1
4 2599
John Harman wrote:
SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");


Try something like:

select difference( 'Fred', 'Fred' );

To see if the function you are trying to use works. If the function
works, there are better changes that the real query with the function
works also. If it doesn't work, then there is no way that the query
would work with it.

I have never seen the difference() function and I was not able to find
it from the MySQL manual, where did you find it?
Jul 20 '05 #2
Hi,

Good idea, still returns the same syntax error.

Looking elsewhere, I think difference is part of the Spatial functions being
added with 4.1

Any ideas how I could achieve the same effect without it?

Thanks for your help so far.

John
"Aggro" <sp**********@yahoo.com> wrote in message
news:b9***************@read3.inet.fi...
John Harman wrote:
SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");


Try something like:

select difference( 'Fred', 'Fred' );

To see if the function you are trying to use works. If the function
works, there are better changes that the real query with the function
works also. If it doesn't work, then there is no way that the query
would work with it.

I have never seen the difference() function and I was not able to find
it from the MySQL manual, where did you find it?

Jul 20 '05 #3
John Harman wrote:
I'm trying to do a MySQL Query using Mysql 3.23.58 something like that below

SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");

The difference piece doesn't seem to be working (Syntax Error returned). Is
this not possible without upgrading the server? or am I doing something
wrong, all I want to do is be able to return the results closest to that
entered first.


How about this?

SELECT C.name
FROM customers C
WHERE C.name LIKE 'Fred'
ORDER BY
ABS(ORD(UPPER(SUBSTRING(C.name,1,1)))
- ORD(UPPER(SUBSTRING('Fred',1,1)))),
ABS(ORD(UPPER(SUBSTRING(C.name,2,1)))
- ORD(UPPER(SUBSTRING('Fred',2,1)))),
ABS(ORD(UPPER(SUBSTRING(C.name,3,1)))
- ORD(UPPER(SUBSTRING('Fred',3,1)))),
ABS(ORD(UPPER(SUBSTRING(C.name,4,1)))
- ORD(UPPER(SUBSTRING('Fred',4,1))));

This is a hack -- it isn't scalable to an arbitrary depth of similarity
between strings, but after 4 or 5 characters, it might be adequate to
sort your dataset accurately.

Regards,
Bill K.
Jul 20 '05 #4
Aggro wrote:
John Harman wrote:
SELECT name FROM customers WHERE name LIKE "Fred" ORDER BY
difference(name,"Fred");


SELECT name FROM customers WHERE name LIKE '%Fred%'
^^^^^^^^

--
Edd Benson
eb****@netscape.net

amoebae leave no fossils
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by John Harman | last post: by
9 posts views Thread by copx | last post: by
19 posts views Thread by Yoon Soo | last post: by
4 posts views Thread by Frank Wallingford | last post: by
5 posts views Thread by Ricky W. Hunt | last post: by
21 posts views Thread by dragoncoder | last post: by
104 posts views Thread by Beowulf | last post: by
3 posts views Thread by rcamarda | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.