Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    162

    Unanswered: sort by relenvance

    How to sort output by relevance, kinda like http://www.dbforums.com/mysql/163304...und-first.html ?

    a search for say 'ad'
    from a table with
    aa
    ab
    ac
    ad
    ae
    af
    ag

    should give me
    ad
    aa
    ab
    ac
    ae
    af
    ag

    In other words, sort by relevance then alphabetically.

    select *
    from tb1
    where @name like tb1.name
    order by tb1.name -- need something more here

    any ideas or thoughts?


    thanks in advance

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    want to post this magical code you have that returns the results set you are showing us?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    mrpcguy, as Brett pointed out, you example looks incomplete or incorrect as each value is in the first list only once. However, if you meant to have ad in the list twice, I think this might get you what you want (or at least closer):
    Code:
    WITH TEST_TAB (COL1)
      AS (
      SELECT 'AA' UNION ALL
      SELECT 'AB' UNION ALL
      SELECT 'AC' UNION ALL
      SELECT 'AD' UNION ALL
      SELECT 'AD' UNION ALL
      SELECT 'AD' UNION ALL
      SELECT 'AE' UNION ALL
      SELECT 'AE' UNION ALL
      SELECT 'AF'
         )
    SELECT *
    FROM TEST_TAB
    GROUP BY COL1
    ORDER BY COUNT(*) DESC, COL1
     
    COL1
    ----
    AD
    AE
    AA
    AB
    AC
    AF
     
    (6 row(s) affected)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Very nice..but I was assuming that Oppie was or wants to do a look up

    Col LIKE '%AD%'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Posts
    162
    Quote Originally Posted by Brett Kaiser View Post
    want to post this magical code you have that returns the results set you are showing us?
    Is this magical enough for you?

    DECLARE @name varchar(2)
    SET @name = 'AD'
    SELECT name
    FROM T1
    ORDER BY DIFFERENCE (name, @name) DESC, name

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try something like...
    Code:
    WITH tb1 (name) AS (
      SELECT 'aa'  UNION ALL
      SELECT 'ab'  UNION ALL
      SELECT 'ac'  UNION ALL
      SELECT 'ad'  UNION ALL
      SELECT 'ae'  UNION ALL
      SELECT 'af'  UNION ALL
      SELECT 'ag' 
    )
    SELECT *
     FROM  tb1
     ORDER BY
           CASE name WHEN 'ad' THEN 0 ELSE 1 END
         , name
    ;

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ORDER BY DIFFERENCE ????

    News to me
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does that do for you ?

    DIFFERENCE (Transact-SQL)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2004
    Posts
    162
    search for 'AD' gives me a value of 4 and the rest a value of 3, i simply sort by that value with DIFFERENCE and then alphabetically

  10. #10
    Join Date
    Mar 2004
    Posts
    162
    Quote Originally Posted by tonkuma View Post
    Try something like...
    Code:
    WITH tb1 (name) AS (
      SELECT 'aa'  UNION ALL
      SELECT 'ab'  UNION ALL
      SELECT 'ac'  UNION ALL
      SELECT 'ad'  UNION ALL
      SELECT 'ae'  UNION ALL
      SELECT 'af'  UNION ALL
      SELECT 'ag' 
    )
    SELECT *
     FROM  tb1
     ORDER BY
           CASE name WHEN 'ad' THEN 0 ELSE 1 END
         , name
    ;
    nice thoughts to use CASE in sort, very usefull. thx.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SOUNDEX is based on phonetic similarity, and its a poor method of comparing text values.

    Here's a function which is more appropriate:
    Code:
    create function [dbo].[CompareText]  (@String1 varchar (50), @String2 varchar (50))
    returns integer
    --Function CompareText
    --blindman 4/2005, Adapted from MS Access algorithm developed 1997
    --Returns value between 0 and 100 indicating the similarity between two character strings.
    --usage: select * from [Table] where dbo.CompareText([ColumnValue], 'SearchString') > 80
    ----blindman, 6/5/2008: Added space before strings to improve accuracy.
    
    begin
    --Test parameters
    -- declare	@String1 varchar(50)
    -- declare	@String2 varchar(50)
    -- set	@String1 = 'George W. Bush'
    -- set	@String2 = 'Curious George'
    
    declare @Possibles integer
    declare @Hits integer
    declare @Counter integer
    
    set	@String1 = ' ' + ltrim(rtrim(@String1))
    set	@String2 = ' ' + ltrim(rtrim(@String2))
    
    set @Possibles = len(@String1) + len(@String2) - 2
    set @Hits = 0
    
    set @Counter = len(@String1)-1
    while @Counter > 0
    	begin
    	  if charindex(substring(@String1, @Counter, 2), @String2) > 0 set @Hits = @Hits + 1
    	  set @Counter = @Counter - 1
    	end
    
    set @Counter = len(@String2)-1
    while @Counter > 0
    	begin
    	  if charindex(substring(@String2, @Counter, 2), @String1) > 0 set @Hits = @Hits + 1
    	  set @Counter = @Counter - 1
    	end
    
    return (100*@Hits)/@Possibles
    --select (100*@Hits)/@Possibles
    
    end
    GO
    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
  •