Question

Q) Find the instructor earning the second highest salary. (Don’t use ORDER BY and LIMIT in your solution.) in MySQL

here is the table I am using named Instructor with the following 4 attributes:
Instructor(ID, name, dept_name, salary)

ID is the primary key

I am supposed to report the answer with the name, ID and salary of the second highest salaried instructor.

I know how to do it if I only needed to report the second highest salary, but I am having trouble getting it with the corresponding name and id as well. This is what I am trying:

Select ID, name, max(salary) as salary
From instructor
Where salary < (select max(salary)
               From instructor);


but it is saying "Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'database.instructor.ID'; this is incompatible with sql_mode=only_full_group_by"


Any help is appreciated!

Answer 1

ans) Select ID, name, max(salary) as salary From instructor Where salary < (select max(salary) From instructor);

Is right and it works properly but when we execute that code it show an error message that is "In aggregated query without GROUP BY, expression #1 of SELECT list contains non aggregated column 'database.instructor.ID'; this is incompatible with sql_mode=only_full_group_by" this means we cannot execute two attributes simultaneously without using GROUP BY command because this is how the configuration is done in mysql so if we want to change the configuration file than we have to write some code in it to resolve this error that is :-

code ---

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

This will change the global sql mode that is set to Group by to sql_mode so that we can execute all the commands.

Config File: /etc/mysql/my.cnf

Variable name : sql_mode OR sql-mode

you can change it directly if you are using XAMPP server or phpMyAdmin that's if you are using phpMyAdmin then change the "sql_mode" setting as mentioned the in below screenshot.

Edit "sql mode" variable and remove the "ONLY_FULL_GROUP_BY" text from the value.

HOPE YOU GOT IT.