srinath_deepika MySQL solution-
select (salary * months)as earnings ,count(*) from employee group by 1 order by earnings desc limit 1;
navdeep_singhAsked to answer Great :)
Mehtasachin PLEASE EXPLAIN
FreeFly19 ... 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
wushengzhong What if they have multiple people tie with maximum earnings?
shankarpentyala The count function keeps count of the people having the same earnings ,since we are grouping by earnings.
sephi1Asked to answer well done
yugoliang what does the group by 1 do?
prakruti7 'SELECT SALESMAN_NAME, SUM(SALES) FROM SALES GROUP BY 1' it will group by SALESMAN_NAME.
bishaltamang11 1 here is the column number, so if you are taking select name,id from employee, name is the 1st column and id is 2nd. so if you want to group it by name so you can write group by 1, or if you want to group it by id then group by 2.
veraalena5555 SELECT MAX(months * salary) ||' '|| COUNT(*) FROM employee GROUP BY salary HAVING MAX(months * salary) = (SELECT MAX(months * salary) from employee);
batur Why you limited selection just with 1 person? What will be the answer if two or three people will have the same equal total salary and it will be equal?
crazy_vikas23 i think limit is applied after all group by and order by clauses
latteo This solution works 2 times slowly than subquery
WHERE salary * months = (select max(salary * months) FROM `Employee`)
ashukpratap here why ?
prome_nabid Because for each row selected, it will execute the where clause (so thus the select statement in subquery)
ndvo2710 For more literal code:
select salary*months as earnings, count(*) from employee group by earnings order by earnings desc limit 1;
For more lazy:
select salary*months as earnings, count(*) from employee group by 1 order by 1 desc limit 1
To use WHERE:
select salary*months, count(*) from employee where salary*months = (select max(salary*months) from employee)
Ankur_Beginning Will you please explain me?
zerofactiongames The order by statement is unecessary as you can sort the grouped items using asc or desc. You can simplify slightly by using
SELECT MONTHS * SALARY AS EARNINGS, COUNT(*) FROM EMPLOYEE GROUP BY EARNINGS DESC LIMIT 1;
MAbdElRaouf Thank you for sharing this.
sanjeev1696 why count() will give u the value as 1,... what count() represents?
zedelta Considering perfromance, "To use WHERE:" would be more resource consuming than the other two. Nevertheless it is a solution :). My favorite is "For more lazy:"
pretty_evils great fast query
psaez nice =D
cbodhisatya the last solution with the where clause does not work
saur2702 rownum = 1 top limit any will work in this case
FYI :)
himanshumittal12 group by salary*months should also be there.
divyabansal30 [deleted]gcunha1996 Thank you, but you should also add a Group By clause at the end of the query using Where, like you did on the other queries!
soumyaa_itian I am getting wrong answer for this.
mgomezmunoz92 [deleted]
amitasaurus [deleted]anadig This wont work in 'Oracle' because group by is executed before select in SQL . Am I right?
anukarsh_007 Amazing :)
jayraj18011997 this is not working for oracle
a_anand_IT110 Its a nice solution but can you explain this particular part
GROUP BY 1
zerofactiongames Adityaanand,
When referring to the code
select salary*months as earnings, count(*) from employee group by 1
"group by 1" is simply stating that you want to group by the data in Column 1.
Since the columns are created in the order they are declared, column 1 or just "1" in this case, is "earnings" since it is declared first.
Therefore in this situation: "Group by 1" is just shorthand for "Group by Earnings".
Cheers.
onkar_rana27 SELECT MAX(MONTHS*SALARY), COUNT(EMPLOYEE_ID) FROM EMPLOYEE where MONTHS*SALARY=(select max( MONTHS*SALARY) from EMPLOYEE);
sruthimallarapu Instead of SELECT MAX(MONTHS*SALARY) can we use SELECT MONTHS*SALARY? If not no can you explain me y?
nimish_gupta you don't use max then how your query able to output the max of the months*salary?
skitram Thats why there is order by months x salary desc 1 segment that shows the only one row with max value
nimish_gupta Then you should see this in mysql!! :)
select max(months*salary), count(*) from employee where salary*months = (select max(months*salary) from employee);
skitram That's right too..every problem has number of solutions it just depends on how you approach it. :-) happy coding!!!
nimish_gupta basically I write in this way coz I'm not good at group by and order by.. Can u plz help me on these two.!!
divya_gracie MS SQL
select top 1 salary*months, count(name) from employee group by salary*months order by 1 desc;
draghusqlbi [deleted]
phdapplymike Very beautiful
mansisaxena_0402 This is not working in MySQL
nsirimailbox In sql server its Top 1 in mysql its limit 1
tadeuaugusto Hello, There..
This is my first post on discussions.. Here is a solution for ORACLE:
SELECT * FROM (SELECT MAX(MONTHS*SALARY), COUNT(EMPLOYEE_ID) FROM EMPLOYEE GROUP BY MONTHS*SALARY ORDER BY MONTHS*SALARY DESC) WHERE ROWNUM = 1;
farhadvaghari THIS IS AMAZING. THANK YOU.
raghusrinivas1 select max(salary*months),count(max(salary*months)) from employee group by (salary*months); can you please explain whats wrong with this query??
abhilash_psp you are printing multiple values. you haven't put any limits.
ajay16kumar71 [deleted]
harshitguptaary1 MS SQL Solution
select max(months*salary),count(months*salary) from employee where employee_id in( select employee_id from employee where months*salary in (select max(months*salary) from employee));
rshaghoulian MySQL solution
From my HackerRank solutions.
SELECT salary * months AS earnings, COUNT(*) FROM Employee GROUP BY earnings ORDER BY earnings DESC LIMIT 1;
Let me know if you have any questions.
haardiksharma For ORACLE junkies : select salary*months,count(*) from employee where salary*months = (select max(salary*months) from employee) group by salary*months;
This will work faster then using the Having Condition.
Initially I tried using - select salary*months,count(*) from employee group by salary*months having salary*months = (select max(salary*months) from employee);
But in this case the query cost was damn too high because of obvious reasons.
IwonaS88 I wanted to use rownum (Oracle) and came with sth like:
select * from (select earn, count(name) from (select months*salary earn, name from employee) group by earn order by earn desc) where rownum = 1;
Could it be simplified somehow? What do you think?
rahulkanodiya You can do it without rownum. select max(earnings), max(count(earnings)) from (select salary*months earnings, employee_id from employee) group by earnings;
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT * FROM ( SELECT Months * Salary, COUNT(*) FROM Employee GROUP BY (Months * Salary) ORDER BY Months * Salary DESC ) WHERE ROWNUM = 1;
Feel free to ask if you have any questions :)
sachin2213sharma for mssql select concat(max(months*salary),' ',count(*)) from employee where months*salary=(select max(months*salary) from employee)
eagle_ray is it efficient query?
select max(salary*months), count(employee_id) from employee where salary*months in (select max(salary*months) from employee);
gulshan_hasanli select (select max(months * salary) from employee), count(*) from employee where months * salary = (select max(months * salary) from employee)
Sort 304 Discussions, By:
Please Login in order to post a comment