CODEWITHSUNDEEP
sql
Vanilla Face
Vanilla Face

Reputation: 916

Small SQL task with a basic table

okay, so we've got practise table "employees" it has these attributes: fname (first name),lname (last name),bdate (birth date), salary, dno (department number).

The mini task is for me to list count all the employees in each departments, group by that department, and select a leader for each department according to the biggest age.

I can only extract lowest birth date in each department, yet I can't pull out first name and last name of the dude which is oldest in department. I wrote this line:

SELECT dno,COUNT(*),MIN(bdate) FROM employees GROUP BY dno;

I have no idea how to make it so that instead of a birth date it would write the first name and last name of the employee with the lowest birthdate.

Can you help me? I learn pretty quick but I can't understand the manual too well. I only guess I need to use HAVING function.

Upvotes: 0

Views: 133

Answers (2)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

select e.dno, total, e.bdate, fname, lname
from (
        SELECT 
            dno,
            COUNT(*) as total,
            MIN(bdate) as bdate,
        from employees
        GROUP BY dno
    ) s
    inner join
    employees e on e.dno = s.dno and e.bdate = s.bdate
order by dno

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

select e.dno, e.fname, e.lname, em.Count
from employees e
inner join (
    select dno, min(bdate) as minbdate, count(*) as Count
    from employees
    group by dno
) em on e.dno = em.dno and e.bdate = em.minbdate

Upvotes: 4

Related Questions