Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: first record in a subgroup and rownum

    I'm trying to find the first record by sub group in a table. For instance, I'd like to find the first employee row found for each department when ordered by salary. I know I have done similar things before, but due to my Alzheimer's cannot remember how to do it. Ie., something similar to:

    select employee_id,max(salary)
    from hr.employees
    group by department_id;

    Any help would be appreciated.

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Use the row_number() analytic function partitioned by department and then just select those rows where the row_number column equals one.

    Alan

  3. #3
    Join Date
    May 2004
    Posts
    184
    Alan,

    Thanks. That's awesome! That isn't anything like I remember using before, but that is very powerful to know. Thanks.

    Robert

Posting Permissions

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