Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tacere
Starting Member
3 Posts |
 Posted - 2007-04-02 : 13:06:48
|
| First off here is a generic version of the statement that I am trying to have work.SELECT *, COUNT( c ) AS rank FROM ( SELECT d.col1, d.col2, d.col3, '1' c FROM tbl1 d INNER JOIN tbl2 t ON d.col0 = t.col0 WHERE d.col1 LIKE '%[search_term]%' AND d.col8 = 0 AND t.col9 = 1 UNION ALL SELECT d.col1, d.col2, d.col3, '1' c FROM tbl1 d INNER JOIN tbl2 t ON d.col0 = t.col0 WHERE d.col2 LIKE '%[search_term]%' AND d.col8 = 0 AND t.col9 = 1 UNION ALL SELECT d.col1, d.col2, d.col3, '1' c FROM tbl1 d INNER JOIN tbl2 t ON d.col0 = t.col0 WHERE d.col3 LIKE '%[search_term]%' AND d.col8 = 0 AND t.col9 = 1) tempsGROUP BY temps.col1, temps.col2, temps.col3, temps.c ORDER BY rank DESC* NOTE: This will work just fine iff I remove the ntext column form the selects (col2 I believe in this example).Moral of the story; I am trying to create an internal search of the site that I am working on so that records that have the [search_term] in col1 to have a higher rank in the results than if it was in both col2 and col3.Please help. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-04-02 : 13:12:25
|
| What's your question. If you mean, why can't you group by ntext - because SQL Server isn't about to try and start grouping records based on values that can be up to a billion characters long. |
 |
|
|
tacere
Starting Member
3 Posts |
Posted - 2007-04-02 : 13:32:32
|
| I understand and that makes sense but is there a way to do something like this? Basically the heart of this is.records that appear multiple times will get a higher rank based on their text and not a sortOrder column in the table. |
|
|
|
tacere
Starting Member
3 Posts |
|
|
|
|
|
|
|
ntext column within a group by record set