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 |
|
RDOllc
Starting Member
2 Posts |
 Posted - 2011-03-11 : 18:00:11
|
| Hello all, I have a large database of user profile data. I need to be able to compare 1 row against all of the other rows, for all columns to find the best match. There are around 30 columns of data for each row. I need to be able to find the 5 best matching rows. I.E.Row 1 matches on 2 column'sRow 2 matches on 12 column'sRow 3 matches on 7 column'sRow 4 matches on 4 column'sRow 5 matches on 9 column'sRow 6 matches on 23 column'sRow 7 matches on 29 column'sIn the end it will show me the data for rows 7, 6, 2, 5, 3Now to make it even harder, Some of the columns are bit, some are varchar. Some of them may be a direct match, some will need to search if the column for row2 contains the text of row 1.FYI, this is on a web app using asp.net if that matters.Thank you! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-11 : 18:21:47
|
Possible solution here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=157456There's no real shortcut for comparing the columns using the structure you have.quote:
some will need to search if the column for row2 contains the text of row 1
I have no idea what this means. It's best to post your actual table structure, some sample data, and the expected results. |
 |
|
|
RDOllc
Starting Member
2 Posts |
Posted - 2011-03-11 : 19:08:27
|
| Thank you but that link dosn't help me.Is there a way to have a stored procedure to:A) Create a temp tableB) Store the uniqueidentifier of each row in the primary DBC) View all columns for the 1st users rowD) find all rows that match column1 then store "1" in the "count" column for that uniqueidentifier in the temp DBE) repeat through all columns of the database, adding a number to the count for each matchF) return the uniqueidentifier for the top 5 highest counts |
|
|
|
|
|
|
|
|
Comparing rows