Geek asks and answers

Find the two best employees who have the highest salary,

Find the two best employees who have the highest salary.

Table name salary, columnsname,salary

We can execute this request using the limit command as

select * from salary order by salary DESC limit 0,2

But how to do this without using the top and limit?

+3
source share
8 answers

I believe this interview question is trying to direct you to nested choices or general tabular expressions or something like that. TOP 2- A simple answer, and, obviously, TOPwas implemented just for this purpose - the interview wants you to do this "manually."

-. () , , , , , 3 .

MySQL -

():

select row_count, * from salary order by salary desc

:

select * from <nested select> where row_count < 3

, MySQL, SQL Server.

SQL Server, :

declare @Salaries table
(
   id int,
   salary money
)

insert into @salaries (id, salary)
values (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 4)  -- A duplicating salary

;WITH Props AS
(
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY salary desc) AS RowNumber
    FROM @Salaries
)
SELECT * FROM Props WHERE RowNumber < 3

4 5.

Sachin Kainth

, . SQL Server:

declare @Salaries table
(
   id int,
   salary money
)

insert into @salaries (id, salary)
values (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 4)

select * from @salaries where salary in -- "in" introduces the problem
(
SELECT MAX(E1.Salary)  
FROM @salaries E1, @salaries E2
WHERE E1.Salary < E2.Salary

union

SELECT MAX(Salary)  
FROM @salaries
)

3, 4 5. 4 5. , where salary in 3, 4 5, ( 3 4).

+4

SQL: 2008 FETCH FIRST 10 ROWS ONLY . , .

SELECT * FROM salary ORDER BY salary DESC FETCH FIRST 2 ROWS ONLY
+1

MySQL:

SET @row := 0;
SELECT name, salary FROM
(SELECT name, salary, @row := @row + 1 AS Row FROM salary ORDER BY salary DESC)
  AS derived1
WHERE Row < 3

. , . , , , , , .

, - : " ?"

, :

SELECT name, salary FROM
(SELECT name, salary, @row := @row + 1 AS Row FROM (SELECT @row := 0) AS d1, salary)
  AS d2
WHERE Row < 3
+1
select * from salary where salary in
(
SELECT MAX(E1.Salary)  
FROM Salary E1, Salary E2
WHERE E1.Salary < E2.Salary

union

SELECT MAX(Salary)  
FROM Salary
)
0
+------+
| Sal  |
+------+
| 3500 | 
| 2500 |
| 2500 | 
| 5500 |
| 7500 |
+------+

Nth Maximum.

select SAL from EMPLOYEE E1 where 
 (N - 1) = (select count(distinct(SAL)) 
            from EMPLOYEE E2 
            where E2.SAL > E1.SAL )
0

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `salary` int(10) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

Mysql Query n- n n-

SELECT salary 
FROM emp 
WHERE salary = (SELECT DISTINCT(salary) 
                FROM emp AS e1 
                WHERE (n) = (SELECT COUNT(DISTINCT(salary)) 
                             FROM emp AS e2 
                             WHERE e1.salary <= e2.salary))
0
SELECT e1.EmployeeID, e1.LastName, COUNT(DISTINCT e2.EmployeeID) AS sals_higher
FROM Employees e1
INNER JOIN Employees e2 ON e1.EmployeeID < e2.EmployeeID
GROUP BY e1.EmployeeID
HAVING sals_higher <= 2
ORDER BY e1.EmployeeID DESC

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_union3 , 2 max EmployeeID.

0

` ,

    select * from salary s1 where 2>=(select count(distinct id) from salary s2
 where s1.salary<=s2.salary) order by salary desc;

. .

,

id Salary
1  1200
2  12345
3  123456
4  2535436 


id salary
4  2535436
2  123456
0

More articles:


All Articles