View Full Version : Order results by closest match

12 Jan 2006, 09:26 AM
I've created a keyword search and would like to order my results on the results page by closest match.

The website I've created is for a musical instrument wholesaler that stocks over 5000 different products. So I wanted a search box the searches the entire product database. I've created this ok and filtered my results recordset like this:

FROM Stock
WHERE Catalogue_Number LIKE 'MMColParam%'OR Section LIKE 'MMColParam%' OR Brand_Name LIKE 'MMColParam%' OR Item_Name LIKE 'MMColParam%' OR Keyword_Search LIKE 'MMColParam%' OR Instrument_Type LIKE 'MMColParam%' OR Intrument_Style LIKE 'MMColParam%'
ORDER BY Section, Catalogue_Number

MMColParam = Request.QueryString("txtKeywordSearch")

Because this filters more than one column of my database I can't just order it by that column. Is there some SQL I can add to order it by closest match? Or is this something a stored procedure would be used for?

I'm using DreamweaverMX2004 with Microsoft Access at the moment. I confess I don't know anything about stored procedures and only enough about databases to get by (I am looking into learning mySQL as I am aware that Access is not really suitable any more).

This is the address for my site.
There are still some pictures missing etc. as its just up for testing at the moment. If you go to www.bandm.co.uk you will see a temporary page instead. This is by far the biggest project I've done so if anybody has comments or suggestions I would love to hear them.


18 Jan 2006, 08:35 AM
You can't really do this directly in SQL. you could get the results then process them in your code but this isn't very efficient.

In SQL server you can do this fairly easily. This article goes into detail :