| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-25-11, 08:49
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 37
|
|
|
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
|
|
07-25-11, 09:11
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
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....
|
|
07-25-11, 09:34
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 37
|
|
|
|
The table is not updated it is related to the final year of a project.
|
|
07-25-11, 09:48
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
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);
|
|
07-26-11, 06:34
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 37
|
|
|
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 :
Quote:
|
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:
Quote:
|
WHERE t1.department = SELECT DISTINCT (department) FROM your_table
|
|
|
07-26-11, 09:24
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 37
|
|
|
where is error?
In order to find out who is the oldest employee of each department I try this statement
Quote:
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
|
|
07-26-11, 09:43
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
Quote:
Originally Posted by krontrex
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.
Quote:
|
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.
|
|
07-26-11, 10:27
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 37
|
|
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
|
|
07-26-11, 11:39
|
|
Registered User
|
|
Join Date: Nov 2003
Posts: 2,408
|
|
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).
|
|
07-27-11, 07:03
|
|
Registered User
|
|
Join Date: Sep 2010
Posts: 37
|
|
|
Yes!!!
Yes This is definitely a faster solution. THANK YOU!!
|
Last edited by krontrex; 07-27-11 at 07:05.
Reason: delting mistypings
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
writing subquery
