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 220.127.116.11.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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading