Max and Min sal with employee name in one query
display the maximum of average salary of each department
sql min and max in same query
select the minimum and maximum salaries from each department in employee table
select max(salary), min(salary), avg(salary from employees)
write a query to find maximum salary in table
select * from emp
select name of employee with max(salary)
I'm working on one SQL query.
I want to get the
MIN sal with their employee names in SQL.
I know how to do with either
MIN. But how can we do it both in one query?
I need a single row output like below:
e1.name AS MaxName, MAX(e1.sal) AS MaxSalary, e2.name AS MinName, MIN(e2.sal) AS MinSalary
In a single select:
SELECT MIN( salary ) AS MinSalary, MIN( name ) KEEP ( DENSE_RANK FIRST ORDER BY salary ASC ) AS MinName, MAX( Salary ) AS MaxSalary, MAX( name ) KEEP ( DENSE_RANK LAST ORDER BY salary ASC ) AS MaxName FROM Employees;
Basic SELECT statement: Get the maximum and minimum salary of , Basic SELECT statement: Get the maximum and minimum salary of all employees Code: SELECT MAX(salary), MIN(salary) FROM employees; Server version 5.6.33-0ubuntu0.14.04.1 /*!40101 SET NAMES utf8 */;. SELECT MAX(salary), MIN(salary) FROM employees; Pictorial Presentation of the above query. MySQL Code Editor: MySQL Online | Online editor and compiler. Facebook Sign In Google Sign In. Password confirmation. Forgot your password? Didn't receive confirmation instructions? Facebook Sign In Google Sign In. Required Invalid E-Mail address OK.
Using Analytic function:
SQL> SELECT MIN(ename) KEEP (DENSE_RANK FIRST ORDER BY sal) min_name, 2 MIN(sal) AS min_sal, 3 MAX(ename) KEEP (DENSE_RANK LAST ORDER BY sal) AS max_name, 4 MAX(sal) AS max_sal 5 FROM emp; MIN_NAME MIN_SAL MAX_NAME MAX_SAL ---------- ---------- ---------- ---------- SMITH 800 KING 5000
Using an In-line view:
SQL> WITH DATA AS 2 ( SELECT MIN(sal) min_sal, MAX(sal) max_sal FROM emp 3 ) 4 SELECT 5 (SELECT e.ename FROM DATA t, emp e WHERE e.sal = t.min_sal AND ROWNUM =1 6 ) min_name, 7 (SELECT t.min_sal FROM DATA t, emp e WHERE e.sal = t.min_sal AND ROWNUM =1 8 ) min_sal, 9 (SELECT e.ename FROM DATA t, emp e WHERE e.sal = t.max_sal AND ROWNUM =1 10 ) max_name, 11 (SELECT t.max_sal FROM DATA t, emp e WHERE e.sal = t.max_sal AND ROWNUM =1 12 ) max_sal 13 FROM dual; MIN_NAME MIN_SAL MAX_NAME MAX_SAL ---------- ---------- ---------- ---------- SMITH 800 KING 5000
Examples of subselect queries, Example 1: Select all columns and rows from the EMPLOYEE table. the DEPARTMENT table) and department name (DEPTNAME) of all employees who were born Example 4: Select the job (JOB) and the minimum and maximum salaries SQL> select ename 2 , deptno 3 , sal 4 from ( 5 select ename 6 , deptno 7 , sal 8 , max (sal) over (partition by deptno) max_sal 9 , min (sal) over (partition by deptno) min_sal 10 from emp 11 ) 12 where sal = max_sal 13 or sal = min_sal 14 order by deptno, sal 15 / ENAME DEPTNO SAL ----- ----- ----- KISHORE 10 1300 SCHNEIDER 10 5000 CLARKE 20
Assuming that you are using Oracle, try this. Here first we are getting rownumber in ascending and descending order and then doing a cross join.
with employee(id,name,sal) as (select 1,'a',1000 from dual union all select 3,'c',1500 from dual union all select 2,'b',2000 from dual) --temp table to recreate the scenario , enew as( select e.*,row_number() over (order by sal) as salasc,row_number() over (order by sal desc) as saldesc from employee e ) --temp table to find the rownumber in ascending and descending order --original query select * from (select id as minsalid,name as minsalempname,sal as minsal from enew where salasc=1) cross join (select id as maxsalemp,name as maxsalempname,sal as maxsal from enew where saldesc=1)
sql query for nth highest & lowest salary with employee name, SELECT TOP 1 salary,Name FROM ( SELECT DISTINCT TOP n salary max(salary) from employee) , (select min(salary) from employee)) SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO; The following query will include the duplicate values. SELECT * FROM EMP WHERE (DEPTNO,SAL) IN ( SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO)
The following solution works for MySQL, which was one of the tags you originally had when you posted your question.
You can perform a
CROSS JOIN of the
employees table against itself to find the max name/salary with a query which finds the min name/salary.
SELECT e1.name AS MaxName, MAX(e1.sal) AS MaxSalary, e2.name AS MinName, MIN(e2.sal) AS MinSalary FROM employees e1 CROSS JOIN employees e2
Click the link below for a running demo. I actually include the name/salary pairs, though you can remove the names if you don't want them there.
SQL query to find second highest salary?, SELECT name, MAX(salary) AS salary FROM employee WHERE salary (SELECT MAX(salary) FROM employee);. One way as suggested by Arka Poddar. IN SQL To find Nth highest salary is a very common interview question if you are going for the role at junior level. Most of the people learn it character by character, but few understand how this query works. How good or bad its performance is? In this post, we will learn these things. I am using MySQL database for demonstration. Table of Contents 1.
Try something like:
select max(sal), min(sal), employee_id from employees group by employee_id;
After that you can join it to get the name. Maybe you can group by name and id too.
How to find Nth highest salary from a table, As an analytic function, DENSE_RANK computes the rank of each row returned In the above query the rank is returned based on sal of the employee table. SELECT name, MAX(salary) as salary FROM employee We can nest the above query to find the second largest salary. SELECT name, MAX(salary) AS salary FROM employee WHERE salary < (SELECT MAX(salary) FROM employee);
SQL: MAX Function, The SQL MAX function is used to return the maximum value of an expression in a SELECT MAX(salary) AS "Highest salary" FROM employees; As a result, "Highest salary" will display as the field name when the result set is returned. SELECT name, MAX(salary) AS salary FROM employee WHERE salary < (SELECT MAX(salary) FROM empRead MoreSELECT sal FROM emp ORDER BY sal DESC LIMIT 1, 1; the simplest way is order your salary table as. SELECT name, MAX(salary) AS salary FROM employee WHERE salary < (SELECT MAX(salary) FROM empShow Less.
DB2 Universal Database V8 Application Development Certification Guide, Suppose you wanted to combine the minimum and maximum salary for each of In the example, the first query calculates the minimum salary of the employee table. Because there can be a first name that is the same as a last name, use a write a query to find the third maximum salary of a employee.(table:employ col name:salary).. Answer / shrikant. SELECT MIN(sal) FROM emp WHERE. sal IN (SELECT distinct TOP 3 sal FROM emp ORDER BY sal. DESC) select max(sal) from emp where sal<(select max(sal) from emp. where sal<( select max(sal) from emp));
[PDF] Answers to 60 SQL lab queries Queries on the , --1. List all the employees' details. SELECT * FROM EMPLOYEES. --2. List all List only the fields first name,last name,salary, commission for all employees List out the department wise maximum salary, minimum salary, average salary of. Hi, Will, If you want to chose no more than one row, use the analytic ROW_NUMBER function, like this: WITH got_r_num AS ( SELECT e.* , ROW_NUMBER OVER ( ORDER BY sal DESC , hiredate , ename ) AS r_num FROM scott.emp WHERE deptno = 20 ) SELECT * -- or list the columns you want FROM got_r_num WHERE r_num
- decide database tag first
- You could use
- Do you want your result as two records, or one record with two columns?
- @TimBiegeleisen, I need one record with two columns. As two records i can do it by union..!! :)
- 4 bits of information in two columns? What should the result look like?
- It is not handling duplicate salaries properly.Two or more employees with maximum salary and/or two or more employees with minimum salary, it will display partial result.
- I'm looking for like this e1.name AS MaxName, MAX(e1.sal) AS MaxSalary, e2.name AS MinName, MIN(e2.sal) AS MinSalary. Just one record
- @drill Done. See the update. Using an inline view. Please mark it as answered, would help others too!
- you did that !! Thanks Alot !!
- If there are two people who have the same min/max salary then the updated query will error with
ORA-01427: single-row subquery returns more than one row.
- @MTO To avoid the
ORA-01427in the in-line query is to restrict the rows using
ROWNUM. Updated with corrected query. That's the disadvantage of in-line view. Also not good in performance. FIRST and LAST are appropriate.
- @Tim, Not working. ORA-00937 "not a single-group group function"
- Why didn't you tag your question with Oracle?
- Am I going in wrong direction ? query requirement makes sense or not ?