https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198698#M57496 <P>Hello,<BR /> Try this if you don't have overlapping records.</P> <PRE><CODE>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 </CODE></PRE> <P>We may also have many movies with same rating:</P> <PRE><CODE>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 </CODE></PRE> <P>updated query per user input (use as it is)</P> <PRE><CODE>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 </CODE></PRE> Thu, 09 Jan 2014 10:17:46 GMT linu1988 2014-01-09T10:17:46Z https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198697#M57495 <P>i have a two tables<BR /> one is rating <BR /> user_id=xxxx<BR /> movie_id = zzzz<BR /> rating = yyyy </P> <P>second is movie<BR /> movie_id = kkkk<BR /> name = pppp</P> <P>using this field i want to find the 20 top rated moive name </P> Mon, 28 Sep 2020 15:36:48 GMT https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198697#M57495 changwoo 2020-09-28T15:36:48Z https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198698#M57496 <P>Hello,<BR /> Try this if you don't have overlapping records.</P> <PRE><CODE>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 </CODE></PRE> <P>We may also have many movies with same rating:</P> <PRE><CODE>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 </CODE></PRE> <P>updated query per user input (use as it is)</P> <PRE><CODE>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 </CODE></PRE> Thu, 09 Jan 2014 10:17:46 GMT https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198698#M57496 linu1988 2014-01-09T10:17:46Z https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198699#M57497 <P>you can move "sort" and "head" before "join" as well, for little better performance.</P> Thu, 09 Jan 2014 14:44:48 GMT https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198699#M57497 somesoni2 2014-01-09T14:44:48Z https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198700#M57498 <P>Thank you for the suggestion, i have changed it <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P> Thu, 09 Jan 2014 16:40:46 GMT https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198700#M57498 linu1988 2014-01-09T16:40:46Z https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198701#M57499 <P>| join is not working.. </P> <P>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</P> Mon, 28 Sep 2020 15:37:28 GMT https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198701#M57499 changwoo 2020-09-28T15:37:28Z https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198702#M57500 <P>sourcetype=rate|fields rate_user_id,rate_movie_id,rate_rating,rate_duration|top 1 rate_rating by rate_movie_id</P> <P>this is working very well </P> <P>i deleted the space and inserted "|" </P> <P>but no result is comming out . </P> <P>this is my search command</P> <P>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</P> Mon, 28 Sep 2020 15:37:31 GMT https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198702#M57500 changwoo 2020-09-28T15:37:31Z https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198703#M57501 <P>do I have to add comparing command?</P> <P>looking to my search command there is no comparing command</P> <P>like rate_movie_id quals movie_id</P> Mon, 28 Sep 2020 15:37:39 GMT https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198703#M57501 changwoo 2020-09-28T15:37:39Z https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198704#M57502 <P>rate_movie_id and movie_id are related you will get the result or we are doing nothing with this query.</P> Mon, 28 Sep 2020 15:37:42 GMT https://community.splunk.com/t5/Splunk-Search/subsearch-help/m-p/198704#M57502 linu1988 2020-09-28T15:37:42Z