Help Ordering Records

[DogBot]

I have a database with two fields: ID and SVCS.
SVCS contains a comma delimited list.
So records on the dabase might look like this:
ID - SVCS
1 - a,c,e,g
2 - b,d,f,h
3 - a,b,c
4 - f,g,h
5 - a,b,c,e

To search this DB a user checks a series of checkboxes on a form, for example:
SVCS: a[x] b[x] c[x] d[ ] e[x] f[ ] g[ ] h[ ]

I want to pull any records where any of the items selected by the user
is found in the SVCS field.

So I take the resulting list from the form: a,b,c,e
and create a query by looping through it as follows:

<cfquery name="SearchResults">
Select * from my_table where
<cfset count=0>
<cfloop index="i" list="#FORM.SVCS#">
<cfset count=count+1>
(SVCS LIKE '%#i#%')<cfif count lt listlen(FORM.SVCS)>OR</cfif>
</cfloop>
</cfquery>
NOTE:I could also use the "IN" statement instead of "LIKE" but I don't think
it would make a diference for my purpouses.

That returns:

1 - a,c,e,g (3 matches: a,c,and e)
2 - b,d,f,h (1 match: b)
3 - a,b,c (2 matches: a,c)
5 - a,b,c,e (4 matches: a,b,c,and e)

THE QUESTION:
How do I order the results by the number of matches?

[Dan Bracuk]

Normalize your database and you won't have problems like this.

[drforbin1970]

I agree with comment here, normalize your database! You should not have a DB
field with comma-delimited values. BUT - if you inherited this task, here's a
plan. Assuming you are using MSSQL, use the routine you have to load matching
records into a table variable, ex. @holding_table, making sure @holding_table
has a column you can use for tallying number of matches.

After your routine loads records to @holding_table, re-run your routine
against @holding_table and update the tally column for each record when you get
a match. Then you can, as the last line in your code:

select * from @holding table order by tally_column desc

You could also use your routine and CF's QueryNew, QueryAdd, etc. to achieve
the same results if you don't use MSSQL.

If you are not familiar with table variables, research them, they are
extremely useful and quick.

[JaredJBlackburn]

If I'm understand you correctly, the length of the string in svcs should grow
with the number of matches. So ORDER BY len (svcs) should order the output
ascending or descending based on the number of matches.

SELECT * FROM my_table
WHERE svcs LIKE '%#i#%'
OR svcs LIKE '%#i#%'
ORDER BY len(svcs) desc

[JaredJBlackburn]

Another way is to simply add up the number of matches for each record - either
in the select clause or in the order by.

... ORDER BY
CASE WHEN var like '%11%' THEN 1 ELSE 0 END +
CASE WHEN var like '%12%' THEN 1 ELSE 0 END +
CASE WHEN var like '%13%' THEN 1 ELSE 0 END

- OR -

SELECT ...,
CASE WHEN var like '%11%' THEN 1 ELSE 0 END +
CASE WHEN var like '%12%' THEN 1 ELSE 0 END +
CASE WHEN var like '%13%' THEN 1 ELSE 0 END AS theRank
FROM ...
WHERE...
ORDER BY theRank

Put the logic in the SELECT if you want to return the number of matches with
the result set.