how do i ORDER BY most matches in a record

[Guest_imported]

hi everybody, i am sorry to bother you...i have an sql question <br>i am doing a search routine using the following sql: <br><br>select distinct BRITEM,BRDESC,BRPIC1,BRPHAS,BRLINK,BRDROP,BRSTCK<br>from pwbits<br>where ((UCASE(BRITEM) like 'THI%' or<br>        UCASE(BRITEM) like '% THI%' or<br>        UCASE(BRKWR1) like 'THIS%' or<br>        UCASE(BRKWR2) like 'THIS%' or<br>        UCASE(BRKWR3) like 'THIS%' or<br>        UCASE(BRDESC) like 'THI%' or<br>        UCASE(BRDESC) like '% THI%')<br><br>        or<br><br>       (UCASE(BRITEM) like 'CHE%' or<br>        UCASE(BRITEM) like '% CHE%' or<br>        UCASE(BRKWR1) like 'CHEES%' or<br>        UCASE(BRKWR2) like 'CHEES%' or<br>        UCASE(BRKWR3) like 'CHEES%' or<br>        UCASE(BRDESC) like 'CHE%' or<br>        UCASE(BRDESC) like '% CHE%'))<br><br>        order by BRITEM for fetch only<br><br><br>basically its a web search engine...i am searching for any records that <br>have the words 'like' or 'cheese' <br>my problem is that i would like to show first the records that have both words ('like' and 'cheese') and then the the records<br>that have just one of the words ('like' or 'cheese')...you know sort <br>of a percentage search thing.... <br>is it possible in sql with a select statement...the table is already created... <br>thanks before hand and sorry about taking your time, <br>adiel

 

[stoleru]

hi. I'm giving you an &quot;Oracle style&quot; solution, with more adjustment it will work on your situation:<br><br>select distinct BRITEM, BRDESC, BRPIC1, BRPHAS, BRLINK, BRDROP, BRSTCK,2 as criteria<br>from pwbits<br>where (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRITEM) like '%THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRITEM) like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR1) like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR1) like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR2) like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR2) like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR3) like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR3) like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRDESC) like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRDESC) like '%CHEESE%')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp<br>union all<br>select distinct BRITEM, BRDESC, BRPIC1, BRPHAS, BRLINK, BRDROP, BRSTCK,1 as criteria<br>from pwbits<br>where (<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRITEM) like '%THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRITEM) not like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRITEM) not like '%THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRITEM) like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR1) like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR1) not like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR1) not like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR1) like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR2) like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR2) not like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR2) not like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR2) like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR3) like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR3) not like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRKWR3) not like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRKWR3) like '%CHEESE%') or<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRDESC) like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRDESC) not like '%CHEESE%')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(UCASE(BRDESC) not like %THIS%' and<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UCASE(BRDESC) like '%CHEESE%')<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp<br>order by 8 DESC,1 for fetch only.<br><br>It creates a new column depending on the type&nbsp;&nbsp;of the condition in the WHERE clause. If the row meets both condition it &quot;wins&quot; a value 2 of criteria, if it meets only one condition, it &quot;wins&quot; a value 1 of criteria. I hope I've written this clear enough. Good luck.<br>Regards, <p>Eduard Stoleru<br><a href=mailto:e_stoleru@yahoo.com>e_stoleru@yahoo.com</a><br><a href= > </a><br>

 

[Guest_imported]

how about if they type in a search for 3,4 or even more words...is there another way to do it that would handle this?&nbsp;&nbsp;(ex. search for: 'this' 'blue' 'cheese' 'swiss')<br><br>Thanks,<br>adiel

 

[MalcolmW]

This is not the code, but the approach I would use.<br>You could simplify your code by concatenating your search fields.<br>Then do a count for each word that you are searching for.<br>Then find the the minimum value of all the the counts (0 for no hits on one or more words, etc)<br>Order the results in descending order based on the minimum value of all counts, so the rows with the all the words would come first, followed by the rows with almost all of the words, etc.<br>The actual code I'll leave to someone needing a serious hobby! <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Guest_imported]

Malcolm Thank You,<br>I'm still having a hard time understanding what you mean...<br>could you give me an example with lets say searching only in BRITEM and BRDESC...<br><br>Thanks before hand,<br>adiel

 

[Guest_imported]

UPDATE:<br><br>Malcolm, I tried to make code from your instructions as follows:<br><br>select britem,brdesc from pwbits<br>where ucase(britem¦¦brdesc) like '%THIS%'<br>or<br>ucase(britem¦¦brdesc) like '%CHEE%'<br><br>i cant figure out the count part...i am using ibm db2...<br>not sure what the count function is...any suggestions??<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

 

[MalcolmW]

CREATE TABLE Hits (BRITEM varchar(?), Phrase varchar (50), Count1 int NOT NULL, Count2 NOT NULL)<br>INSERT INTO TABLE<br>select BRITEM,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Bread Cheese',<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM(CASE WHEN ucase(britem¦¦brdesc) like '%THIS%'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;THEN 1 <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE O END) AS Count1,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM(CASE WHEN ucase(britem¦¦brdesc) like '%CHEE%' <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;THEN 1 <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE O END) AS Count2<br>from pwbits<br>HAVING (Count1 &gt; 0 OR Count 2 &gt; 0)<br>--not sure if DB2 will let you use the alias names Count1 and Count2 here, otherwise use the full definition SUM(CASE... used to define the aliases)<br>--OK, so that gets you a temporary result of the hits, now to order it correctly.<br>SELECT BRITEM,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Phrase,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CASE WHEN Count1 &gt; Count2 THEN Count2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE Count1 END AS BothHits,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CASE WHEN Count1 &gt; Count2 THEN Count2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE Count1 END AS SingleHits<br>ORDER BY BothHits DESC,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SingleHits DESC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>--again, not sure if DB2 will let you use aliases BothHits and SingleHits in the Order by, otherwise use the alias definition.<br>This is written without testing, so my usual warranty does not apply <br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>