We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
I can confirm that GROUP BY clearly seems to work for Oracle, altho remember that the table ALIAS works differently there as they forbid the 'AS' keyword for table names (it is ok with col names):
SELECT MAX_EARNINGS, COUNT(EMPLOYEE_ID) FROM (SELECT MAX(EARNINGS) AS MAX_EARNINGS FROM (SELECT MONTHS * SALARY AS EARNINGS FROM EMPLOYEE) EARNINGS_TABLE) MAX_EARNINGS_TABLE, EMPLOYEE WHERE MONTHS * SALARY = MAX_EARNINGS GROUP BY MAX_EARNINGS ;
Without indentation (HR wants us to enter these things as one long line!) I consider these completely write-only, who can make sense of that without indentation?? But, it works.
we can reduce number of sub queries here.
my version for above query :
select (salarymonths)earning,count() from employee where (salarymonths)=(select max(salarymonths) earning from employee) group by (salarymonths);
Top Earners
You are viewing a single comment's thread. Return to all comments →
I don't think
GROUP BY <alias>works for Oracle. Can someone verify?I can confirm that GROUP BY clearly seems to work for Oracle, altho remember that the table ALIAS works differently there as they forbid the 'AS' keyword for table names (it is ok with col names):
SELECT MAX_EARNINGS, COUNT(EMPLOYEE_ID) FROM (SELECT MAX(EARNINGS) AS MAX_EARNINGS FROM (SELECT MONTHS * SALARY AS EARNINGS FROM EMPLOYEE) EARNINGS_TABLE) MAX_EARNINGS_TABLE, EMPLOYEE WHERE MONTHS * SALARY = MAX_EARNINGS GROUP BY MAX_EARNINGS ;
we can reduce number of sub queries here. my version for above query : select (salarymonths)earning,count() from employee where (salarymonths)=(select max(salarymonths) earning from employee) group by (salarymonths);