how to find most similar datas

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

General SQL Server Forums

New to SQL Server Programming

how to find most similar datas

Author

Topic

Sny
Starting Member

4 Posts

Posted - 2011-05-20 : 18:14:54

I have two simple tables like below;

reader(reader_id, name, address)
action(actionid,date,type, bookid, reader_id)

(type -> Borrowed,Hold,Return)

I want to find 50 most similar readers who read the same exact book. I can not think about it how to do this . Please help me

Thank you

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-05-20 : 20:42:24

A bit messy but this should give you what you need. Then if you want to do a calc based upon percentage of books read to books in common then you can.
Note reader will show up twice in query, once as a reader and once as a compare to for each reader.

----------------------------------------
select top 50 c.reader_id,c. compare_reader_id,br.totalbooksRead,br2.TotalbooksRead as TotalbooksRead_Compare,c.booksInCommon
from
(
select a.reader_id,b.reader_id as compare_reader_id,count(*) as booksInCommon
from
(
select r.reader_id,a.bookid
from #reader r
join #action a on a.reader_id = r.reader_id
) a
join
(
select r.reader_id,a.bookid
from #reader r
join #action a on a.reader_id = r.reader_id
) b on a.bookid = b.bookid and a.reader_id <> b.reader_id
group by a.reader_id,b.reader_id
) c
join
(
select r.reader_id,count(*) as TotalbooksRead
from #reader r
join #action a on a.reader_id = r.reader_id
group by r.reader_id
) br on br.reader_id = c.reader_id
join
(
select r.reader_id,count(*) as TotalbooksRead
from #reader r
join #action a on a.reader_id = r.reader_id
group by r.reader_id
) br2 on br2.reader_id = c.compare_reader_id
order by booksInCommon desc

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-21 : 08:02:09

I think this should work.. if syntax isnt correct, the having clause will do the trick
select name from
reader r join action a on r.reader_id = a.reader_id
group by name
having count(actionid) >= 50

Your key to software development =>http://www.itexposed.com

Sny
Starting Member

4 Posts

Posted - 2011-05-21 : 16:01:59

Thank you so much, this is really clear for me..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources