What Is the Simplest SQL Query to Find the Second Largest Value

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

SELECT MAX( col )
  FROM table
 WHERE col < ( SELECT MAX( col )
                 FROM 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 find the Second largest value from a table.?

Try this: this should give the second largest salary:

SELECT MAX(EmpSalary) FROM employee WHERE EmpSalary < (SELECT MAX(EmpSalary) FROM employee);

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..

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;

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!

How to get second highest value among multiple columns in SQL

You can unpivot your data then make the query as below

SELECT name,flags,flag FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY name ORDER BY flag DESC) rn,*
    FROM
    (
        SELECT name, flag, flags
        FROM
        (
          SELECT 'json' name, 500 flag1,400  flag2,200 flag3, 100 flag4
          UNION ALL
          SELECT 'Mark' name, 400 flag1,299  flag2,250 flag3, 183 flag4
          UNION ALL
          SELECT 'Tom' name, 932 flag1,331  flag2,283 flag3, 844 flag4
        ) AS cp
        UNPIVOT 
        (
          flag FOR flags IN (flag1, flag2, flag3, flag4)
        ) AS up
    )x
)y
WHERE rn=2

p.s. In the middle of the query I just simulate your data with bunch of selects and union all

p,s Also you can get the first amount by this query too, look at rn = 2

---

Related Topics