subsearch help

[changwoo]

i have a two tables
one is rating
user_id=xxxx
movie_id = zzzz
rating = yyyy

second is movie
movie_id = kkkk
name = pppp

using this field i want to find the 20 top rated moive name

[linu1988 · Accepted solution]

Hello,
Try this if you don't have overlapping records.

sourcetype=xx|fields user_id,movie_id,rating|sort - rating|head 20|join movie_id[|search sourcetype=yy|fields movie_id,name]|table movie_id,movie_name,rating

We may also have many movies with same rating:

sourcetype=xx|fields user_id,movie_id,rating|top 20 rating by user_id,movie_id|fields user_id,movie_id,rating|join movie_id[|search sourcetype=yy|fields movie_id,movie_name]|table movie_id,movie_name,rating

updated query per user input (use as it is)

sourcetype=rate |fields rate_user_id,rate_movie_id,rate_rating,rate_duration|sort - rate_rating | head 20| rename rate_movie_id as movie_id | join movie_id [search sourcetype = movie | fields movie_id, movie_name]|table movie_id, movie_name, rate_rating

[linu1988]

rate_movie_id and movie_id are related you will get the result or we are doing nothing with this query.

[changwoo]

do I have to add comparing command?

looking to my search command there is no comparing command

like rate_movie_id quals movie_id

[changwoo]

sourcetype=rate|fields rate_user_id,rate_movie_id,rate_rating,rate_duration|top 1 rate_rating by rate_movie_id

this is working very well

i deleted the space and inserted "|"

but no result is comming out .

this is my search command

sourcetype=rate|fields rate_user_id,rate_movie_id,rate_rating,rate_duration|top 1 rate_rating by rate_movie_id | join rate_movie_id[ | search sourcetype= movie | fields movie_id, movie_name] | table movie_id, movie_name, rate_rating

[changwoo]

| join is not working..

sourcetype=rate |fields rate_user_id,rate_movie_id,rate_rating,rate_duration|sort -rate_rating | head 20| join rate_movie_id[|search sourcetype = movie | fields movie_id, movie_name]|table movie_id, movie_name, rate_rating

[linu1988]

Thank you for the suggestion, i have changed it 🙂

[somesoni2]

you can move "sort" and "head" before "join" as well, for little better performance.