What is the simplest SQL Query to find the second largest value

Question

What is the simplest SQL query to find the second largest integer value in a specific column    There are maybe duplicate values in the column

User · Answer

select   from emp e where 3 gt   select count distinct salary      from emp where s salary lt  salary    This query selects the maximum three salaries  If two emp get the same salary this does not affect the query

User · Answer

select max COL NAME  from TABLE NAME where COL NAME in       select COL NAME from TABLE NAME where COL NAME  lt   select max COL NAME  from TABLE NAME      subquery returns all values other than the largest  select the max value from the returned list

User · Answer

Microsoft SQL Server - Using Two TOPs for the N-th highest value  aliased sub-query    To solve for the 2nd highest   SELECT TOP 1 q    FROM  SELECT TOP 2 column name FROM table name ORDER BY column name DESC  as q ORDER BY column name ASC    Uses TOP twice  but requires an aliased sub-query  Essentially  the inner query takes the greatest 2 values in descending order  then the outer query flips in ascending order so that 2nd highest is now on top  The SELECT statement returns this top   To solve for the n-th highest value modify the sub-query TOP value  For example   SELECT TOP 1 q    FROM  SELECT TOP 5 column name FROM table name ORDER BY column name DESC  as q ORDER BY column name    Would return the 5th highest value

User · Answer

SELECT   FROM EMP WHERE salary           SELECT MAX salary  FROM EMP            WHERE salary     SELECT MAX salary  FROM EMP

User · Answer

select top 1 MyIntColumn from MyTable where  MyIntColumn  lt  gt   select top 1 MyIntColumn from MyTable order by MyIntColumn desc  order by MyIntColumn desc

User · Answer

We can also make use of order by and top 1 element as follows   Select  top 1 col name from table name where col name  lt   Select top 1 col name from table name order by col name desc  order by col name desc

User · Answer

select score  from table  where score    select max score -1 from table

User · Answer

Using a correlated query   Select   from x x1 where 1    select count    from x where x1 a  lt  a

User · Answer

Try       select a    b   from   select   from  select ROW NUMBER   OVER ORDER BY fc amount desc  SrNo1  fc amount as amount1 From entry group by fc amount  tbl where tbl SrNo1   2  a    select   from  select ROW NUMBER   OVER ORDER BY fc amount asc  SrNo2  fc amount as amount2  From entry group by fc amount  tbl where tbl SrNo2  2  b

User · Answer

select   from  select ROW NUMBER   over  Order by Col x desc  as Row  Col 1     from table 1 as table new tn inner join table 1 t1     on tn col 1   t1 col 1 where row   2   Hope this help to get the value for any row

User · Answer

SELECT    FROM  employee  WHERE  employee salary    SELECT employee salary   FROM employee  GROUP BY employee salary ORDER BY employee salary DESC LIMIT   1 1

User · Answer

select MAX salary  as SecondMax from test where salary    select MAX salary  from test

User · Answer

This works in MS SQL   select max  COLUMN NAME   from  TABLE NAME  where  COLUMN NAME   lt      select max  COLUMN NAME   from  TABLE NAME

User · Answer

select extension from  dbo   Employees  order by extension desc offset  2  rows fetch next  1 rows only

User · Answer

This is an another way to find the second largest value of a column Consider the table  Student  and column  Age  Then the query is   select top 1 Age from Student where Age in select distinct top 2 Age  from Student order by Age desc  order by Age asc

User · Answer

It is the most esiest way   SELECT       Column name FROM       Table name  ORDER BY        Column name DESC LIMIT 1 1

User · Answer

At first make a dummy table without max salary then query max value from dummy table  SELECT max salary  from  Select   FROM emp WHERE salary lt  gt   SELECT MAX salary  from emp   temp

User · Answer

I suppose you can do something like   SELECT   FROM Table ORDER BY NumericalColumn DESC LIMIT 1 OFFSET 1   or  SELECT   FROM Table ORDER BY NumericalColumn DESC LIMIT  1  1    depending on your database server  Hint  SQL Server doesn t do LIMIT

User · Answer

MSSQL  SELECT      FROM  Users      order by UserId desc OFFSET 1 ROW  FETCH NEXT 1 ROW ONLY    MySQL  SELECT      FROM Users     order by UserId desc LIMIT 1 OFFSET 1   No need of sub queries     just skip one row and select second rows after order by descending

User · Answer

A very simple query to find the second largest value  SELECT  Column  FROM  Table  ORDER BY  Column  DESC LIMIT 1 1

User · Answer

This is very simple code  you can try this  -  ex   Table name   test  salary   1000 1500 1450 7500   MSSQL Code to get 2nd largest value  select salary from test order by salary desc offset 1 rows fetch next 1 rows only    here  offset 1 rows  means 2nd row of table and  fetch next 1 rows only  is for show only that 1 row  if you dont use  fetch next 1 rows only  then it shows all the rows from the second row

User · Answer

Old question I know  but this gave me a better exec plan    SELECT TOP 1 LEAD MAX  column   OVER  ORDER BY column desc   FROM TABLE   GROUP BY column

User · Answer

Query to find the 2nd highest number in a row-   select Top 1  salary  from XYZ where Salary not in  select distinct TOP 1 salary  from XYZ order by Salary desc  ORDER BY Salary DESC   By changing the highlighted Top 1 to TOP 2   3 or 4 u can find the 3rd  4th and 5th highest respectively

User · Answer

select min sal  from emp where sal in       select TOP 2  sal  from emp order by sal desc    Note  sal is col name emp is table name

User · Answer

select col name from       select dense rank   over  order by col name desc  as  rank   col name     from table name   withrank  where rank   2

User · Answer

Something like this  I haven t tested it  though   select top 1 x from     select top 2 distinct x    from y    order by x desc   z order by x

User · Answer

SELECT MAX  col     FROM table  WHERE col  lt    SELECT MAX  col                    FROM table

User · Answer

select max column name  from table name where column name not in  select max column name  from table name     not in is a condition that exclude the  highest value of column name    Reference   programmer interview

User · Answer

SELECT MAX col  FROM table WHERE col NOT IN  SELECT MAX col  FROM table

User · Answer

See How to select the nth row in a SQL database table    Sybase SQL Anywhere supports   SELECT TOP 1 START AT 2 value from table ORDER BY value

User · Answer

SELECT MAX Salary  FROM Employee WHERE Salary NOT IN  SELECT MAX Salary  FROM Employee     This query will return the maximum salary  from the result - which not contains maximum salary from overall table

User · Answer

SELECT         FROM      table  WHERE      column  lt   SELECT max columnq  FROM table   ORDER BY      column DESC LIMIT 1

User · Answer

In T-Sql there are two ways   --filter out the max select max  col   from  table  where col  lt         select max  col       from  table     --sort top two then bottom one select top 1 col  from       select top 2 col      from  table      order by col  topTwo order by col desc    In Microsoft SQL the first way is twice as fast as the second  even if the column in question is clustered   This is because the sort operation is relatively slow compared to the table or index scan that the max aggregation uses   Alternatively  in Microsoft SQL 2005 and above you can use the ROW NUMBER   function   select col from       select ROW NUMBER   over  order by col asc  as  rowNum   col     from  table    withRowNum  where rowNum   2

User · Answer

select age from student group by id having age lt  select max age  from student order by age limit 1

User · Answer

SELECT MAX sal  FROM emp WHERE sal NOT IN  SELECT top 3 sal FROM emp order by sal desc     this will return the third highest sal of emp table

User · Answer

select   from  table  where  column   select max column from  table  where column  lt   select max column from  table

User · Answer

Tom  believe this will fail when there is more than one value returned in select max  COLUMN NAME   from  TABLE NAME  section  i e  where there are more than 2 values in the data set   Slight modification to your query will work -  select max  COLUMN NAME   from  TABLE NAME  where  COLUMN NAME    IN        select max  COLUMN NAME   from  TABLE NAME

User · Answer

Use this query  SELECT MAX  colname    FROM Tablename  where colname  lt        SELECT MAX  colname        FROM Tablename

User · Answer

you can find the second largest value of column by using the following query  SELECT   FROM TableName a WHERE   2    SELECT count DISTINCT b ColumnName          FROM TableName b WHERE        a ColumnName  lt   b ColumnName     you can find more details on the following link  http   www abhishekbpatel com 2012 12 how-to-get-nth-maximum-and-minimun html

User · Answer

You can find nth highest value using the following query       select top 1 UnitPrice from  select distinct top n UnitPrice from   Order Details  order by UnitPrice desc  as Result order by UnitPrice asc   Here  the value of n will be 1  for the highest number   2  for the second highest number   3  for the third highest number

User · Answer

The easiest way to get second last row from a SQL table is to use ORDER BYColumnNameDESC and set LIMIT 1 1   Try this   SELECT   from  TableName  ORDER BY  ColumnName  DESC LIMIT 1 1

User · Answer

As you mentioned duplicate values   In such case you may use DISTINCT and GROUP BY to find out second highest value   Here is a table      salary            GROUP BY    SELECT  amount FROM  salary  GROUP by amount ORDER BY  amount DESC  LIMIT 1   1      DISTINCT    SELECT DISTINCT amount FROM  salary  ORDER BY  amount DESC  LIMIT 1   1   First portion of LIMIT   starting index   Second portion of LIMIT   how many value

User · Answer

Very Simple  The distinct keyword will take care of duplicates as well   SELECT distinct SupplierID FROM  Products  order by SupplierID desc limit 1 offset 1

User · Answer

I see both some SQL Server specific and some MySQL specific solutions here  so you might want to clarify which database you need   Though if I had to guess I d say SQL Server since this is trivial in MySQL   I also see some solutions that won t work because they fail to take into account the possibility for duplicates  so be careful which ones you accept   Finally  I see a few that will work but that will make two complete scans of the table   You want to make sure the 2nd scan is only looking at 2 values   SQL Server  pre-2012    SELECT MIN  column   AS  column  FROM       SELECT TOP 2  column       FROM  Table       GROUP BY  column       ORDER BY  column  DESC   a   MySQL   SELECT  column   FROM  table   GROUP BY  column   ORDER BY  column  DESC  LIMIT 1 1   Update   SQL Server 2012 now supports a much cleaner  and standard  OFFSET FETCH syntax   SELECT TOP 2  column   FROM  Table   GROUP BY  column   ORDER BY  column  DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

User · Answer

Simplest of all  select sal from salary order by sal desc limit 1 offset 1

User · Answer

The easiest would be to get the second value from this result set in the application   SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2   But if you must select the second value using SQL  how about   SELECT MIN value  FROM  SELECT DISTINCT value FROM Table ORDER BY value DESC LIMIT 2  AS t

[sql] What is the simplest SQL Query to find the second largest value?

Examples related to sql

Examples related to puzzle