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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: