Thanks to Abishek Kulkarni's solution, on which this optimization is suggested. The above query can also be written as, SELECT * FROM Employee Emp1Īgain, if the queries are as simple as just running on single table and needed for informational purposes only, then you could limit the outermost query to return 1 record and run a separate query by placing the nth highest salary in where clause Mysql have clearly mentioned about this optimization at Here, as soon as inner query finds n distinct salary values greater than outer query's salary, it returns the result to outer query.

WHERE Emp2.Salary > Emp1.Salary LIMIT N)) Edit: Its really the same thing with your table. name, age, country, etc.) whose values you want to fetch. Its basic syntax is: SELECT TOP number percent columnlist FROM tablename Here, columnlist is a comma separated list of column or field names of a database table (e.g. Half meaning if I have 80 rows sorted A-Z I want to be able to select the first 40. SELECT TOP 10 name, totalraces FROM ( SELECT COUNT() as totalraces, name FROM thattable GROUP BY name ) as t1 ORDER BY totalraces DESC If your data is stored differently, the query will be different, obviously. The SQL TOP clause is used to limit the number of rows returned.

However, the trick is to do an improved linear-search. Instead of using the query: SELECT TOP 2 FROM customers I suggest using the query: SELECT FROM customers LIMIT 2 or. Phil Ill sort them by alphabetical order ascending. Sorting all the records first, do consume a lot of time (Imagine if the table contains millions of records). ,rank() over (order by Salary DESC) ranking Where rownumb = n /*n is nth highest salary*/įind the nth highest salary in Oracle using RANK select * FROM ( Row_number() over (order by Salary DESC) rownumb Nth highest salary using the TOP keyword in SQL Server SELECT TOP 1 Salaryįind the nth highest salary in MySQL SELECT Salary FROM Employeeįind the nth highest salary in SQL Server SELECT Salary FROM Employeeįind the nth highest salary in Oracle using rownum select * from ( ORDER BY country, population DESC ) ranked. WHERE (N-1) = ( /* Subquery starts here */ To get the 2 largest cities for each country, you can use the following query in MySQL: FROM (SELECT city, country, population, countryrank : IF( currentcountry country, countryrank + 1, 1) AS countryrank, currentcountry : country. Query-1 SELECT * /*This is the outer query part */ WHERE Salary (select MAX(Salary) from Employee ) WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee) Finding the highest salary select MAX(Salary) from Employee

Mysql select top 1