Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: 5 records per department table

    Hello,

    Please consider following two tables

    employees (employee_id, department_id, employee_name)

    departments ( department_id, department_name)

    There can be thousands of employees per department and what I am looking is I want last five employees from all the departments. That is if there are 3 departments then five employees from department1, five from department2 and five from department3.

    Any help would be appreciated

    Thank you,
    Khwab

  2. #2
    Join Date
    Sep 2009
    Posts
    39
    Quote Originally Posted by khwabsheth
    Hello,

    Please consider following two tables

    employees (employee_id, department_id, employee_name)

    departments ( department_id, department_name)

    There can be thousands of employees per department and what I am looking is I want last five employees from all the departments. That is if there are 3 departments then five employees from department1, five from department2 and five from department3.

    Any help would be appreciated

    Thank you,
    Khwab



    Code:
    
    CREATE TABLE employees  (
       employee_id  integer PRIMARY KEY,
        employee_name text   
    )
    
    CREATE TABLE departments (
       department_id  integer PRIMARY KEY,
       department_name text   
    )
    
    CREATE TABLE emp_dep (
       ID_employee  integer,
       ID_department integer,
       date_of_insert  date default NOW(),
       mytime time default NOW(),
     PRIMARY KEY(ID_employee, ID_department)
    )
    select emp_dep.date_of_insert, emp_dep.mytime, t3.department_name , t2.employee_name from employees t2,departments t3,emp_dep
    where emp_dep.ID_employee=t2.employee_id and emp_dep.ID_department=t3.department_id
    group by emp_dep.date_of_insert,t3.department_name , t2.employee_name, emp_dep.mytime order by emp_dep.date_of_insert desc, emp_dep.mytime desc
    this is for all select , I don't now how select only five for each department.

    I think must be using row numbers over.

    Row numbers over partitions in SQL

    I don't know this.
    Last edited by Fakin; 10-12-09 at 15:36.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by Fakin
    I think must be using row numbers over.
    Something like:
    Code:
    select employee_id, 
           employee_name, 
           deparment_name 
    from (
      select e.employee_id, 
             e.employee_name, 
             d.department_name, 
             row_number() over (partition by d.department_id order by employee_id) as dept_count
      from employees e 
        join emp_dep ed on (e.employee_id = ed.id_employee)
        join departments d on (ed.id_department = d.department_id)
    ) t
    where dept_count <= 5;
    You can use any ORDER BY inside the partition to get the "last" employees according to whatever "last" means for you

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Fakin
    I think must be using row numbers over.

    Row numbers over partitions in SQL
    thank you for linking to my article

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2007
    Posts
    4
    Thanks shammat for your reply but the query you have given is for oracle and it won't work in postgreSQL.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by khwabsheth
    Thanks shammat for your reply but the query you have given is for oracle and it won't work in postgreSQL.
    I does work in Postgres, but you'll need 8.4.

    As you didn't mention your version I assumed you are running the latest one.

  7. #7
    Join Date
    Feb 2007
    Posts
    4
    My mistake I didn't mention the version. I am using 8.3.7

    Any idea how can we run that logic on 8.3.7?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT d.department_id
         , d.department_name
         , e.employee_id
         , e.employee_name
      FROM departments AS d
    INNER
      JOIN employees AS e
        ON e.department_id = d.department_id
       AND ( SELECT COUNT(*)
               FROM employees
              WHERE department_id = e.department_id
                AND employee_id > e.employee_id ) < 5
    "last 5 employees per department" assuming employee_id is increasing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2007
    Posts
    4
    Brilliant. This is what I was looking for.

    Thanks r937.

Posting Permissions

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