SQL - how to get certain column with MIN and MAX id for every department? -

SQL - how to get certain column with MIN and MAX id for every department? -

i'm trying select info using sql, no success. here's i'm trying do.

i have 2 tables:

table employees next columns:

idemployee | name | surname | department_id 1 | john | smith | 1 2 | jane | smith | 1 3 | neo | anderson | 1 4 | john | mason | 2 5 | james | cameron | 2 6 | morpheus| grumpy | 2

table departments columns:

iddepartment | name 1 | thieves 2 | madmen

i want select surnames of first , lastly employees of every section , count of employees. result:

department_name | first_employee | last_employee | employee_count thieves | smith | anderson | 3 madmen | mason | grumpy | 3

i able count , id's of first , lastly employees next query:

select d.iddepartment, count(*) "employee_count", min(e.idemployee) "first_employee", max(e.idemployee) "last_employee" ( employees e inner bring together departments d on d.iddepartment=e.department_id) grouping d.name;

however, can't find right way select surnames. help appreciated.

while there might way, 1 way utilize query subquery:

select d.name department_name, e.surname first_employee, e2.surname last_employee, t.employee_count ( select d.iddepartment, count(*) "employee_count", min(e.idemployee) "first_employee", max(e.idemployee) "last_employee" employees e inner bring together departments d on d.iddepartment=e.department_id grouping d.name ) t bring together employees e on t.first_employee = e.idemployee bring together employees e2 on t.last_employee = e2.idemployee bring together departments d on t.iddepartment = d.iddepartment

and here fiddle: http://sqlfiddle.com/#!2/17a5b/2

good luck.

sql