Help needed in SQL!

[padsp]

Hi,

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.

I am looking for your great help.

Thanks
Padsp

[denevge]

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
)

HTH
Gert

[padsp]

Hi Gert,

Thank you for the query. I appreciate your time for this.

Thanks

Padsp