union with rank duplicated records

  • Hi Everyone.

    I have a problem that I could not figure out what to do with it.

    I have a couple of tables and views. All have the same unique ID.

    When a user try to do a search on our web site we want to try to look in multiple tables and views, Rank the results and aggregate the results back (remove any duplicates).

    Part of the search is a Full Text Index search.

    I created already the following query that seems to be working ok except for one problem, some of the results are duplicated because the Rank is unique.

    select rank,,PL

    From freetexttable (dbo.items,*,'Key Words') as ft

    join Items as I on ft.[Key] = I.ItemNumber

    LEFT OUTER JOIN dbo.IM_90_UDF_IMMasterfile ON ft.[Key]= dbo.IM_90_UDF_IMMasterfile.ItemNumber

    Where (I.Approved = 1 and dbo.IM_90_UDF_IMMasterfile.Web_Exclude<>'Y')

    union

    select '999999',ItemNumber,PL

    from View_Items_Published

    Where (Mfg_Part_Number like '%Key Words%')

    union

    select '999998',ItemNumber,PL

    from View_Items_Published

    Where (ItemNumber like '%Key Words%')

    order by rank desc

    In a way to fix this issue I was suggested to create a Temp table, insert the data from all tables / views into it and group the results.

    The problem with that is I have to Drop the table before/after every search. And lots of other issues (table locks etc’)

    Here is the script for this one:

    Drop table #QueryResult

    CREATE TABLE #QueryResult (Rank int,ItemNumber char(15), PL Char(4))

    INSERT #QueryResult (Rank ,ItemNumber , PL)

    select rank,I.ItemNumber ,PL

    From freetexttable (dbo.items,*,'Key Words') as ft

    join Items as I on ft.[Key] = I.ItemNumber

    LEFT OUTER JOIN dbo.IM_90_UDF_IMMasterfile ON ft.[Key]= dbo.IM_90_UDF_IMMasterfile.ItemNumber

    Where (I.Approved = 1 and dbo.IM_90_UDF_IMMasterfile.Web_Exclude<>'Y')

    INSERT #QueryResult

    select '999999',ItemNumber,PL

    from View_Items_Published

    Where (Mfg_Part_Number like '%Key Words%')

    INSERT #QueryResult

    select '999998',ItemNumber,PL

    from View_Items_Published

    Where (ItemNumber like '%Key Words%')

    Select ItemNumber, Max(Rank) as Rank, PL

    From #QueryResult

    Group by ItemNumber, PL

    order by rank desc

    My question is:

    Any way of aggregating all the results on the fly, I’m sure there is something better.

    Thanks in advance

    Oren Levy

  • Derived table to assemble the ranking, outer query on the derived table to find the max rank for each group and sort on it:

    Select ItemNumber, PL, Max(Rank)

    From

    (

      select rank, As ItemNumber, PL

      From freetexttable (dbo.items,*,'Key Words') as ft

      join Items as I on ft.[Key] = I.ItemNumber

      LEFT OUTER JOIN dbo.IM_90_UDF_IMMasterfile ON ft.[Key]= dbo.IM_90_UDF_IMMasterfile.ItemNumber

      Where (I.Approved = 1 and dbo.IM_90_UDF_IMMasterfile.Web_Exclude<>'Y')

      union All

      select '999999',ItemNumber,PL

      from View_Items_Published

      Where (Mfg_Part_Number like '%Key Words%')

      union All

      select '999998',ItemNumber,PL

      from View_Items_Published

      Where (ItemNumber like '%Key Words%')

    ) dt

    Group By ItemNumber, PL

    Order By Max(Rank) Desc

  • you are unbelievable

    Thank you so much, you have no idea how great this is.

    You rock.....

    Oren Levy

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply