Get The Second Highest Value with Standard Sql

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 max for each value on Oracle

You need conditional aggregation after appyling analytic function such as ROW_NUMBER() :

WITH t2 AS
(
  SELECT client,order_date,
         ROW_NUMBER() OVER (PARTITION BY client ORDER BY order_date DESC) as rk
   FROM order_table
)
SELECT client, 
       MAX(CASE WHEN rk=1 THEN order_date END) AS "max order date",
       MAX(CASE WHEN rk=2 THEN order_date END) AS "2nd highest max ord.date"
  FROM t2
 GROUP BY client

Demo

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

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

Find second max value in the moving window frame using SQL / BigQuery

Short Answer

You can do it like this:

WITH sample AS (
  SELECT 
    ts,
    ROUND(10*RAND(), 1) as value
  FROM UNNEST (GENERATE_ARRAY(1, 10)) ts
)
, sample_with_moving_array AS (
    SELECT
        ts,
        value,
        ARRAY_AGG(value) OVER(ORDER BY ts RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_array,
    FROM sample
)
, sample_with_sorted_moving_array AS (
    SELECT
        ts,
        value,
        moving_array,
        ARRAY(SELECT value FROM UNNEST(moving_array) as value ORDER BY value DESC) as sorted_moving_array
    FROM sample_with_moving_array
)
SELECT
    ts,
    value,
    sorted_moving_array[SAFE_ORDINAL(1)] AS max_value_in_3_frame_alternate_version,
    sorted_moving_array[SAFE_ORDINAL(2)] AS second_max_value_in_3_frame,
FROM sample_with_sorted_moving_array

Explanation

First of all, NTH_VALUE won't work because it is a generalization of the window function FIRST_VALUE, not MAX.

In BigQuery, you can compute the second max value in three steps.
First, use ARRAY_AGG to compute the moving array of values:

ARRAY_AGG(value) OVER(ORDER BY ts RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_array

This will give you something like this:

CREATE TABLE #Dimensions (
    [length] numeric(10, 2),  
    [width] numeric(10, 2),   
    [height] numeric(10, 2)
)
INSERT INTO #Dimensions
    ([length], [width], [height])
VALUES
    (5.60,    3.70,    0.90),
    (13.50,   6.54,    3.50),
    (14.33,   7.95,    3.86),
    (6.42,    6.69,    7.95),
    (12.00,   10.00,   9.00),
    (5.60,    3.70,    3.70)

SELECT c.*
FROM #Dimensions d
CROSS APPLY (
    SELECT 
        [length] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY),
        [width] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 1 ROWS FETCH NEXT 1 ROW ONLY),
        [height] = (SELECT N FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N) ORDER BY N DESC OFFSET 2 ROWS FETCH NEXT 1 ROW ONLY)
) c

----------------------
length  width   height
----------------------
5.60    3.70    0.90
13.50   6.54    3.50
14.33   7.95    3.86
7.95    6.69    6.42
12.00   10.00   9.00
5.60    3.70    3.70

SELECT 
    [length] = c1.N,
    [width] = c2.N,
    [height] = c3.N
FROM #Dimensions d
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c1
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c2
CROSS APPLY (SELECT N, ROW_NUMBER() OVER (ORDER BY N DESC) AS RN FROM (VALUES (d.[length]), (d.[width]), (d.[height])) v(N)) c3
WHERE (c1.RN = 1) AND (c2.RN = 2) AND (c3.RN = 3)