Comparison - Find MOST match result

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Comparison - Find MOST match result

Author

Topic

cutever
Starting Member

32 Posts

Posted - 2002-04-19 : 04:56:18

Do any SQL script/syntax that allow me to perform following operation?

Example:

I have a table as following:

Item ID Item Name Spec1 Spec2 Spec3
----------------------------------------------
1 Fish 5 9 7
2 Dog 12 8 4
3 Flower 7 20 6

I want to pass in Parameter:
@Spec1 = 9
@Spec2 = 3
@Spec3 = 5

System will displays the ‘Most Match’ result

Display Result:

Product à Flower

-----------------------------------------------------------
Can I use CASE ? or any other method to do it ??

Please HELP

ver

Ver

sica
Posting Yak Master

143 Posts

Posted - 2002-04-19 : 05:19:50

Can you explain a little bit more how "Most Match" should return the result?
Why is not the result "Dog"?

//Sica

Edited by - sica on 04/19/2002 05:20:16

Edited by - sica on 04/19/2002 05:20:40

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-04-19 : 06:10:34

Hi I think you are looking for this..........

set nocount on
create table #temp
(
item_id int,
item_name varchar(20),
spec1 int,
spec2 int,
spec3 int
)

--create empty table for holding all close records
select * into #temp1 from #temp where 1=2

insert into #temp values (1,'Fish', 5,9,7)
insert into #temp values (2,'Fish', 12,8,4)
insert into #temp values (3,'Flower', 7,20,6)

declare @spec1 int, @spec2 int, @spec3 int
select @spec1 = 9, @spec2 = 3, @spec3 = 5

--Compare the @spec1 with spec1
insert into #temp1
select * from #temp
where abs(@spec1 - spec1) in (select min(abs(@spec1 - spec1)) from #temp)
or abs(@spec1 + spec1) in (select min(abs(@spec1 - spec1)) from #temp)

--Compare the @spec2 with spec2
insert into #temp1
select * from #temp
where abs(@spec2 - spec2) in (select min(abs(@spec2 - spec2)) from #temp)
or abs(@spec2 + spec2) in (select min(abs(@spec2 - spec2)) from #temp)

--Compare the @spec3 with spec3
insert into #temp1
select * from #temp
where abs(@spec3 - spec3) in (select min(abs(@spec3 - spec3)) from #temp)
or abs(@spec3 + spec3) in (select min(abs(@spec1 - spec3)) from #temp)

select * from #temp1

--#temp2 will contain all the repeated items
select item_id,item_name,count(item_id) qty
into #temp2
from #temp1
group by item_id,item_name

--final answer
select item_id, item_name from #temp2
where qty = (select max(qty) from #temp2)

drop table #temp2
drop table #temp1
drop table #temp

Regards,
Ramesh Singh

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-19 : 06:24:37

quote:

or abs(@spec1 + spec1) in (select min(abs(@spec1 - spec1)) from #temp)

Huh??

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-04-19 : 07:54:45

Explanation for your quote

SET @spec1 = 6
first case:
IF Value of spec in database is 5
--abs(@spec1 - spec1) in (select min(abs(@spec1 - spec1)) from #temp)
1 in (1)
--abs(@spec1 + spec1) in (select min(abs(@spec1 - spec1)) from #temp)
11 in (1)

Second case:
IF Value of spec in database is 7
--abs(@spec1 - spec1) in (select min(abs(@spec1 - spec1)) from #temp)
1 in (1)
--abs(@spec1 + spec1) in (select min(abs(@spec1 - spec1)) from #temp)
13 in (1) ---Oops... Seems to be an error

I will get back to you on monday, since sat,sun is off....

Thanks for pointing out error.

Ramesh Singh

Ramesh Singh

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-19 : 08:14:15

Error? I just couldn't make any sense out of the whole thing! I'd assumed that there was some special meaning for "most match" that you knew and I didn't. But comparing the absolute sum of two numbers against the minimum difference seemed like a pretty strange thing to do in any circumstance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources