How to avoid same ranks while doing a FULLTEXT

[jophinmichael]

Hi guys, I am doing a FULLTEXT search as shown below Select t.*,k.[Rank] From table1 t Join Containstable(table1, column3, 'abcd|efgh|ijkl|mnop') As k On t.Id = k.[Key] Order By k.[Rank] DESC But I want the results to be ranked distinctly. Currently my results are listed something like the below column1 column2 column3 Rank 1 asdc abcd|efgh|ijkl|mnop 99 2 asdad abcd|efgh|ijkl 99 3 cdsdfc abcd 80 4 efsef abcd|efgh 30 5 asasda abcd|efgh|ijkl 30 6 dsas abcd|efgh|ijkl|mnop 30 Also please note that row1 and row6 are having same values for column3 but the ranks are 99 and 30 respectively. Thanks in Advance Jophin jophin

[Scubapro]

I'm not quite sure what you want. If you want row 6 just below row 1 and so on, just add another order filter:

Order By t.column3 [DESC/ASC], k.[Rank] DESC

[jophinmichael]

Hi Scubapro, Select t.*,k.[Rank] From table1 t Join Containstable(table1, column3, 'abcd|efgh|ijkl|mnop') As k On t.Id = k.[Key] Order By k.[Rank] DESC column1 column2 column3 Rank 1 asdc abcd|efgh|ijkl|mnop 99 2 asdad abcd|efgh|ijkl 99 3 cdsdfc abcd 80 4 efsef abcd|efgh 30 5 asasda abcd|efgh|ijkl 30 6 dsas abcd|efgh|ijkl|mnop 30 I am not able to do an order by for the column3 because its data type is ntext. My actual requirement is that row1 and row2 should not have equal ranks because,Row1 is an exact match and Row2 is not. It is fine for me if, Rank of Row2=Rank of Row5 and Rank of Row1=Rank of Row6 because they are having equal values for column3. Kind Regards jophin

[Scubapro]

Try:

Join Containstable(table1, column3, '("abcd|efgh|ijkl|mnop")') As k On t.Id = k.[Key]

[jophinmichael]

Hi Scubapro, Thanks for the reply. But this is not making any difference in the rankings. But we found that the current ranking is somehow ok for us. Thanks for you efforts Regards

jophin