View previous topic :: View next topic
|
Author |
Message |
Debahutyborah
New User
Joined: 08 Aug 2006 Posts: 2 Location: Bangalore
|
|
|
|
Hi,
I want a query which would give me maximum position for a employee
If my table is (Emp number and Position are together the primary key)
EMP Num POSITION
1 A
1 B
1 C
2 D
2 E
3 E
The result should be
1 C
2 E
3 E
Please help. |
|
Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
SELECT EMP_NUM, MAX(POSITION)
FROM TABLE
GROUP BY EMP_NUM;
Hope this will helpful |
|
Back to top |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
You may want to select also another field from the table, for example:
Code: |
SELECT EMP_NUM, MAX(POSITION), EMP_NAME
FROM TABLE
GROUP BY EMP_NUM; |
This will not work. You will have to use:
Code: |
SELECT EMP_NUM, POSITION, EMP_NAME
FROM TABLE T1
WHERE POSITION = (SELECT MAX(POSITION)
FROM TABLE T2 WHERE T1.EMP_NUM = T2.EMP_NUM)
ORDER BY EMP_NUM; |
(The ORDER BY is not mandatory). |
|
Back to top |
|
|
Debahutyborah
New User
Joined: 08 Aug 2006 Posts: 2 Location: Bangalore
|
|
|
|
Thanks Marso . |
|
Back to top |
|
|
|