Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Posts
    40

    Unanswered: writing subquery

    Dear all
    I have a table "employee" with 4 columns:
    name (varchar), srname (varchar), department (varchar), age (INT)
    I need to find the name and last name of the oldest employee in each of the departments.
    I simply could not find a way to group age columns for each department in order to find the maximum of the each group.
    Could someone help me by writing an SQL statement solving this problem.
    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Do you really store the age in the table?

    That way you would need to update that column every day, because the age of a person changes every day....

  3. #3
    Join Date
    Sep 2010
    Posts
    40
    The table is not updated it is related to the final year of a project.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    SELECT name, srname, department, age
    FROM your_table t1
    WHERE age = (SELECT max(t2.age) 
                 FROM your_table t2
                 WHERE t2.department = t1.department);

  5. #5
    Join Date
    Sep 2010
    Posts
    40

    No results

    Hi the solution you gave me I tested. I get no error but no result either. I simply does not understand how this works; one part of the statement is confusing :
    WHERE t2.department = t1.department
    because t1.department is identical to t2.department because t1 and t2 are derived from the same table. I don't see any grouping here. It looks like this statement leads to only one result and that is the oldest employee.
    Shouldn't it be something like:
    WHERE t1.department = SELECT DISTINCT (department) FROM your_table

  6. #6
    Join Date
    Sep 2010
    Posts
    40

    where is error?

    In order to find out who is the oldest employee of each department I try this statement
    SELECT name, srname, department, age FROM your_table t1
    WHERE department IN (SELECT DISTINCT department from your_table) and age = (SELECT max(t2.age)
    FROM your_table t2
    WHERE t2.department = t1.department
    but something is missing here. I don't get the solution. Does anyone know whaer the error is?
    Thanks

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by krontrex View Post
    Hi the solution you gave me I tested. I get no error but no result either.
    That is actually surprising.
    Can you please post the CREATE TABLE Statement for your table and some sample data as INSERT INTO? That would make it easier for us to help you.


    It does work for me:
    Code:
    psql (9.0.4)
    Type "help" for help.
    
    postgres=>
    postgres=> create table employee
    postgres-> (
    postgres(>    name varchar(100),
    postgres(>    srname varchar(100),
    postgres(>    department varchar(10),
    postgres(>    age integer
    postgres(> );
    CREATE TABLE
    postgres=>
    postgres=> insert into employee
    postgres-> (name, srname, department, age)
    postgres-> values
    postgres-> ('n1', 's1', 'd1', 30),
    postgres-> ('n2', 's2', 'd1', 31),
    postgres-> ('n3', 's3', 'd1', 28),
    postgres-> ('n4', 's4', 'd2', 25),
    postgres-> ('n5', 's5', 'd2', 26),
    postgres-> ('n6', 's6', 'd2', 20),
    postgres-> ('n7', 's7', 'd3', 10);
    INSERT 0 7
    postgres=>
    postgres=> commit;
    COMMIT
    postgres=>
    postgres=>
    postgres=> SELECT name, srname, department, age
    postgres-> FROM employee t1
    postgres-> WHERE age = (SELECT max(t2.age)
    postgres(>              FROM employee t2
    postgres(>              WHERE t2.department = t1.department);
     name | srname | department | age
    ------+--------+------------+-----
     n2   | s2     | d1         |  31
     n5   | s5     | d2         |  26
     n7   | s7     | d3         |  10
    (3 rows)
    
    postgres=>
    For each department I get one employee, and that is the oldest one.

    because t1.department is identical to t2.department because t1 and t2 are derived from the same table
    That's called a derived subquery and the two departments are not really from the same table. The first one is from T1 and the second one is from T2. Those are two differen things when the statement is running.

  8. #8
    Join Date
    Sep 2010
    Posts
    40
    I apologize for giving a misleading answer. The query does not return no result but it is timed out (lasts longer than 30 sec)
    Yes I did same as you and it works the problem is that my database has got more than 100.000 entries and maybe that is why the query is so slow. How can I make this query faster?
    Thanks

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    When posting performance problems you should also post the execution plan of that query.

    For a start you can try to create an index on the age column. That should help.

    An alternative query would be this one
    Code:
    SELECT name, 
           srname, 
           department, 
           age, 
    FROM ( 
      SELECT name, 
             srname, 
             department, 
             age, 
             max(age) over (partition by department) as max_age
      FROM employee
    ) t
    WHERE age = max_age
    I tried it with 500,000 rows and that took about 8 seconds on my laptop (and was about two times faster than the other query).

  10. #10
    Join Date
    Sep 2010
    Posts
    40

    Yes!!!

    Yes This is definitely a faster solution. THANK YOU!!
    Last edited by krontrex; 07-27-11 at 08:05. Reason: delting mistypings

Posting Permissions

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