select max values for unique inputs in BD

Question

select max values for unique inputs in BD

Рейтинг: -3Ответов: 1Опубликовано: 25.01.2023

I have several tables(titles, salaries, employees and etc. with one common/key column (emp_no) I need to create/write a request to show the greater/biggest salary for each position. DB has more than 10k inputs. But I need to reflect max salary for each position (there only 7 different position in there an I need to find max salary of each position. I use something like

select titles.title, max(salary) 
from titles 
join salaries on titles.emp_no=salaries.emp_no 
order by max(salary) asc limit 10;

but limit does not work and I cant explain to the sql what I need.

sql sqlite

Источник: Stack Overflow на русском

Answer 1

▲ 0

SELECT titles.title, MAX(salaries.salary) 
FROM titles 
JOIN salaries ON titles.emp_no = salaries.emp_no 
GROUP BY titles.title
ORDER BY MAX(salaries.salary) DESC
LIMIT 10;

Yes, you can use a similar query to find the top 10 employees with the highest average salary. Here's an example query:

SELECT emp_no, AVG(salary) as avg_salary
FROM salaries
GROUP BY emp_no
ORDER BY avg_salary DESC
LIMIT 10;

This query will group the results by the employee number (emp_no) and calculate the average salary for each group using the AVG() function. Then it will order the results by the average salary in descending order, and return only the top 10 rows using the LIMIT clause.

You can also join this query with the employees table to retrieve the employee name

SELECT employees.first_name, employees.last_name, AVG(salaries.salary) as avg_salary
FROM employees
JOIN salaries ON employees.emp_no = salaries.emp_no
GROUP BY employees.emp_no
ORDER BY avg_salary DESC
LIMIT 10;

This query will give you the employee name, employee number and average salary.

select max values for unique inputs in BD

Ответы