I have question. My data is like this:

EMPID BASICPAY DESID DEPTID TRANSDATE
  1      2000     1     1   03-MAY-05
  2      2000     3     2   03-MAY-05
  1      5000     2     3   03-MAY-05
  2      4000     3     2   03-MAY-05
  1      6000     3     1   20-JUN-05
  2    100000     5     4   20-JUN-05

And I need data like this:

EMPID BASICPAY DESID DEPTID TRANSDATE
  1      6000     3     1   20-JUN-05
  2    100000     5     4   20-JUN-05

What I mean is I need all the records of employees to the max of its date, which is grouped by empid. I wrote a query for that but Oracle release 8.0.5.0.0 didn't allow it and gave me an error:

select distinct max(empid), basicpay, desid, deptid, mAX(transdate) from employee where transdate = (select max(transdate) from employee) group by empid

ERROR at line 1:
ORA-00979: not a GROUP BY expression

Please help me.

    Requires Free Membership to View

    Login

To obtain the row having the maximum value for a certain column, for each different group of rows based on another column, you would think you need to use GROUP BY. However, this doesn't work if you also want other columns in the row, not just the one you're grouping on. And if you place those other columns into the GROUP BY, you won't get the results you want, you'll basically get all rows back.

Fortunately, there's another way to do grouping, and that's with a correlated subquery.

select EMPID 
     , BASICPAY 
     , DESID 
     , DEPTID 
     , TRANSDATE
  from yourtable as foo
 where TRANSDATE
     = ( select max(TRANSDATE)
           from yourtable
          where EMPID = foo.EMPID )

Notice the correlation variable FOO. This allows the subquery to reference the value of the EMPID in the outer query, so that the two rows -- the row from the outer query, plus each row in the inner query with the same EMPID -- can be distinguished from each other syntactically.

In English, the above query says "select some columns from this table where the value of this one column is the maximum value from all the rows which have the same value of this other column."

Put more simply, a correlated subquery is another way to do grouping.

This was first published in May 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top