Results 1 to 6 of 6

Thread: About FTS Rank

  1. #1
    Join Date
    Jun 2006
    Posts
    4

    Unanswered: About FTS Rank

    Hi,

    Why adidas is better-ranked than nike??

    Table "indice":

    idcCod fabCod mcaCod catCod fabNom mcaNom catNom
    1 1 1 1 Nike Nike Ropa
    2 2 2 1 Adidas Adidas Ropa
    3 1 1 3 Nike Nike Medias
    4 1 1 8 Nike Nike Largas

    SELECT [RANK] ,[KEY],idc.*
    FROM FREETEXTTABL (dbo.indice,mcaNombre,fabNombre,catNombre),
    'ropa adidas nike',
    LANGUAGE 3082, 5) res left join
    dbo.indice idc with (nolock) on idc.idcCodigo=res.[key]
    ORDER BY [RANK] DESC;

    Result:

    RANK KEY
    18 2
    0 3
    0 4
    0 1

    Why idcCods's 1 and 2 have different rank??
    because idcCod's 3 and 4 affects ?? or it's just thinks of language??

    Thank's in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your code references column idcCodigo, which is not in your indice sample data.

    Your code references column key, which is not in your FREETEXT table.

    Your code references column RANK, which is not in either datasource.

    How are we supposed to help you debug this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2006
    Posts
    4
    Blindman, The fixed SQL query is:

    SELECT [RANK] ,[KEY],idc.*
    FROM FREETEXTTABLE (dbo.indice,mcaNom,fabNom,catNom),
    'ropa adidas nike',
    LANGUAGE 3082, 5) res left join
    dbo.indice idc with (nolock) on idc.idcCod=res.[key]
    ORDER BY [RANK] DESC;

    This may help you in your first observation. You were right.

    "Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) and full-text key (KEY) for each row."

    from

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/4523ae15-4260-40a7-a53c-8df15e1fee79.htm

    Maybe help for 2nd and 3rd...

    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well I understand your question now, but I don't know the answer to your problem. My best guess would be that Addidas ranks higher because it is earlier in your search string 'ropa adidas nike'. Have you tried searching for 'ropa nike adidas', to see if you get different results?

    I am reluctant to use any fuzzy search algorithm such as FREETEXT if I don't have a thorough understanding of its rules...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2006
    Posts
    4
    Thanks blindman,

    Basically the FTS search algorithm uses a complex rank criteria.

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/98c2a062-4093-41ce-8491-c5b3f7f3c6a3.htm

    But the real reason apparently is:

    "...because Nike appears more frequently thank Adidas. The algorithm ranks rarer words higher than words which occur more frequently"

    Hilary (Cotter)

    I've recently receive this response to my question.

    It's my first participation in dbforums, it looks very interesting...thank you and sorry about my defective first post...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Thank you for posting the solution that you found.
    Not many people on here use FREETEXT, so any experience you gain from it would be useful for the forum.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •