Help needed in SQL!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Help needed in SQL!

  1. #1
    Join Date
    May 2001
    Posts
    11
    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

  2. #2
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    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

  3. #3
    Join Date
    May 2001
    Posts
    11
    Hi Gert,

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

    Thanks

    Padsp

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width