Q

When not to use GROUP BY

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.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close