We have 2 tables,
Table1 has following cols and rows:-
user_id
100
101
Table2 has following cols and rows:-
user_id industry_id totalclicks
100 4585 1
100 4856 8
100 4785 1
101 4585 1
101 4856 1
Here are my requirements,
I need display the list of users who clicked maximum value (largest total clicks) and industry id. In above sample rows user 100 clicked 8 times in 4856 industry and user 101 has clicked once for both industry. I do not know how to take out first value in this case.
It's not the most elegant solution, but I think this works :
select distinct userid,industryid
from
(
select a.userid , first_value(b.industryid) over ( partition by a.userid order by b.totalclicks desc ) as industryid
from a,b
where a.userid = b.userid
)
Reply With Quote
Bookmarks