You are viewing a single comment's thread. Return to all comments →
GROUP BY 1
means - group by first column from SELECT .
The same pattern could be used for ORDER BY :
ORDER BY 1
order by earnings
What if they have multiple people tie with maximum earnings?
The count function keeps count of the people having the same earnings ,since we are grouping by earnings.
but still it will output the value of single top output instead of other ones??
Can group by be used with column alias "earning"? I'm confused, thank you so much.
Yes, you can use group by with the column alias 'earning' or by 1 in this case since 1 can be used to refer to the first column in the SELECT statement(earnings).
this is how you can make it work!
select o.* from(select earnings,count(*) from (select (months*salary) as earnings from employee) group by earnings order by earnings desc) o where rownum=1;
I don't think group by 1 works for oracle,can someone please verify??
select max(months*salary),count(months*salary) from Employee where (months*salary) = (select max(months*salary) from Employee);
without group by...........
your solution does not work it says invalid use of group function
Not the most optimal solution but works fine though, thanks
True, better filter out all the unnecessary values first and then do the agregations..
Here's an oracle solution without group by and is generalized incase the question asks for the nth maximum (where Rank = n).
SELECT AVG(EARNINGS), COUNT(*) FROM(
SELECT MONTHS*SALARY AS EARNINGS, RANK() over (order by MONTHS * SALARY DESC) as RANK FROM EMPLOYEE
where RANK = 1
no it doesnt work in oracle
Solution is correct, but can someone explain below statement !
if we see query execution order -> from ,join, where, groupby, having , select, distinct, orderby.
now select comes after groupby , so how goup by 1 has taken first column from select ?
what does count(*) do?
COUNT(*) counts all the names corresponding to MAX earnings.