How to Get Second Largest or Third Largest Entry from a Table

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

SELECT MAX( col )
  FROM table
 WHERE col < ( SELECT MAX( col )
                 FROM table )

How to get second largest or third largest entry from a table

SELECT *
FROM (
  SELECT some_column, 
         row_number() over (order by your_sort_column desc) as row_num
  FROM some_table
) t
WHERE row_num = 3

If you expect more than one row to have the same value in your_sort_column you can also use the rank() function

SELECT *
FROM (
  SELECT some_column, 
         rank() over (order by your_sort_column desc) as row_rank
  FROM some_table
) t
WHERE row_rank = 3

This migh return more than one row..

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

SELECT MAX( col )
  FROM table
 WHERE col < ( SELECT MAX( col )
                 FROM table )

Get the second highest value in a MySQL table

Here's one that accounts for ties.

Name    Salary
Jim       6
Foo       5
Bar       5
Steve     4

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees))

Result --> Bar 5, Foo 5

EDIT:
I took Manoj's second post, tweaked it, and made it a little more human readable. To me n-1 is not intuitive; however, using the value I want, 2=2nd, 3=3rd, etc. is.

/* looking for 2nd highest salary -- notice the '=2' */
SELECT name,salary FROM employees
WHERE salary = (SELECT DISTINCT(salary) FROM employees as e1
WHERE (SELECT COUNT(DISTINCT(salary))=2 FROM employees as e2
WHERE e1.salary <= e2.salary)) ORDER BY name

Result --> Bar 5, Foo 5

Select second largest from a table without limit

Assuming marks is unique, following query gives you the second largest mark.

SELECT   MAX(marks)
FROM     ATable
WHERE    marks < (SELECT MAX(marks) FROM ATable)

To get the entire record, you could wrap this in an INNER JOIN

SELECT  t1.*
FROM    ATable t1
        INNER JOIN (
          SELECT   marks = MAX(marks)
          FROM     ATable
          WHERE    marks < (SELECT MAX(marks) FROM ATable)
        ) t2 ON t2. marks = t1.marks

Query to find second largest value from every group

I think you can do what you want with the project_milestone table and row_number():

select pm.*
from (select pm.*,
             row_number() over (partition by project_id order by completed_date desc) as seqnum
      from project_milestone pm
      where pm.completed_date is not null
     ) pm
where seqnum = 2;

If you need to include all projects, even those without two milestones, you can use a left join:

select p.project_id, pm.milestone_id, pm.completed_date
from projects p left join
     (select pm.*,
             row_number() over (partition by project_id order by completed_date desc) as seqnum
      from project_milestone pm
      where pm.completed_date is not null
     ) pm
     on p.project_id = pm.project_id and pm.seqnum = 2;

Get HIGHEST and SECOND HIGHEST value for each ID (SQL)

This is my solution, it might not be the cleanest but it should work in any SQL.

select a1.equipment, highest_date, max(b.date) as second_highest_date
from
(
select equipment, max(date) as highest_date
from YOUR_TABLE as a
group by equipment
) a1
join YOUR_TABLE as b
on b.equipment = a1.equipment and b.date != a1.highest_date
group by a1.equipment, a1.highest_date

Query to find nᵗʰ max value of a column

You could sort the column into descending format and then just obtain the value from the nth row.

EDIT::

Updated as per comment request. WARNING completely untested!

SELECT DOB FROM (SELECT DOB FROM USERS ORDER BY DOB DESC) WHERE ROWID = 6

Something like the above should work for Oracle ... you might have to get the syntax right first!

R - find first, second and third largest values by row

You can use

# add the columns
df <- cbind.data.frame(df, t(apply(df, 1, function(row_i){
                             sort(row_i, decreasing = TRUE)[1:3]})))
# name the columns
names(df)[(ncol(df)-2):ncol(df)] <- c("first", "second", "third")

# see results
df
  v1 v2 v3 v4  v5 first second third
1  0  5 23  2   4  23     5      4
2  1  6  5 32  23  32     23     6
3  2  3 24  6  65  65     24     6
4  3 21 87 58  87  87     86     58
5  4 24  6  5   4  24     6      5
6  5  7 32 21  12  32     21     12
7  6  8  5  4 115  115    8      6
8  7  9 48  5   5  48     9      5
9  8  6  6 87  24  87     24     8

---

Related Topics