Select Second Most Minimum Value in Oracle

Select second most minimum value in Oracle

Use an analytic function

SELECT value
  FROM (SELECT value,
               dense_rank() over (order by value asc) rnk
          FROM table)
 WHERE rnk = 2

The analytic functions RANK, DENSE_RANK, and ROW_NUMBER are identical except for how they handle ties. RANK uses a sports-style process of breaking ties so if two rows tie for a rank of 1, the next row has a rank of 3. DENSE_RANK gives both of the rows tied for first place a rank of 1 and then assigns the next row a rank of 2. ROW_NUMBER arbitrarily breaks the tie and gives one of the two rows with the lowest value a rank of 1 and the other a rank of 2.

SQL. Is there any efficient way to find second lowest value?

select price from table where price in (
    select 
        distinct price 
    from 
    (select t.price,rownumber() over () as rownum from table t) as x 
    where x.rownum = 2 --or 3, 4, 5, etc
)

How to find the 2nd highest value in a row with Oracle SQL?

This will work with any number of columns, just make sure you add them to the concatenated list where labelled merge_col in my query below:

select col1, col2, col3, col4, col5, second_highest
  from (select x.*,
               regexp_substr(merge_col, '[^|]+', 1, levels.column_value) as second_highest,
               row_number() over(partition by x.col1 order by to_number(regexp_substr(merge_col, '[^|]+', 1, levels.column_value)) desc) as rn
          from (select t.*, col3 || '|' || col4 || '|' || col5 as merge_col
                  from tbl t) x,
               table(cast(multiset
                          (select level
                             from dual
                           connect by level <=
                                      length(regexp_replace(merge_col,
                                                            '[^|]+')) + 1) as
                          sys.OdciNumberList)) levels)
 where rn = 2

Fiddle test: http://sqlfiddle.com/#!4/b446f/2/0

In other words, for additional columns, change:

col3 || '|' || col4 || '|' || col5 as merge_col

to:

col3 || '|' || col4 || '|' || col5 || '|' || col6 .........  as merge_col

with however many columns there are in place of the ......

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

Find max, second max and min value out of multiple columns

For SQL Server 2012+:

Another possible approach to get your expected output using ORDER BY with OFFSET and FETCH:

Table:

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)

Statement:

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

Output:

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

For SQL Server 2008+:

When ORDER BY with OFFSET and FETCH is not supported, approach using ROW_NUMBER() is also a solution:

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)

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's the best way to select the minimum value from several columns?

There are likely to be many ways to accomplish this. My suggestion is to use Case/When to do it. With 3 columns, it's not too bad.

Select Id,
       Case When Col1 < Col2 And Col1 < Col3 Then Col1
            When Col2 < Col1 And Col2 < Col3 Then Col2 
            Else Col3
            End As TheMin
From   YourTableNameHere

Select Min value row in oracle

Update Specific answer :

SQL> WITH Equip_price AS
  2    ( SELECT 1 pid, 1 equipmentID, 50 price FROM dual
  3    UNION ALL
  4    SELECT 2 , 2 , 20 FROM dual
  5    UNION ALL
  6    SELECT 3 , 1 , 100 FROM dual
  7    UNION ALL
  8    SELECT 4 , 1 , 20 FROM dual
  9    UNION ALL
 10    SELECT 5 , 3 , 100 FROM dual
 11    UNION ALL
 12    SELECT 6 , 3 , 10 FROM dual
 13    UNION ALL
 14    SELECT 7 , 1 , 20 FROM dual
 15    )
 16  SELECT ep.pid,
 17         ep.equipmentID,
 18         ep.price
 19   FROM (
 20         SELECT ep.*,
 21                row_number() OVER(PARTITION BY ep.equipmentID ORDER BY ep.price)
 22                  AS row_identifier
 23         FROM Equip_price ep
 24        ) ep
 25   WHERE ep.row_identifier = 1;

       PID EQUIPMENTID      PRICE
---------- ----------- ----------
         4           1         20
         2           2         20
         6           3         10

SQL>

For example, to get the employee details with minimum salary in each department -

SQL> WITH DATA AS
  2    ( SELECT deptno, MIN(sal) min_sal FROM emp GROUP BY deptno
  3    )
  4  SELECT * FROM emp A, DATA b
  5  WHERE A.deptno = b.deptno
  6  AND A.sal = b.min_sal
  7  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO     DEPTNO    MIN_SAL
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20         20     800
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30         30     950
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10         10       1300

You could also use Analytic function like ROW_NUMBER.

SQL> WITH DATA AS
  2    ( SELECT t.*, row_number() OVER(ORDER BY sal) rn FROM emp t
  3    )
  4  SELECT * FROM emp WHERE empno =
  5    (SELECT empno FROM data WHERE rn = 1
  6    )
  7  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

SQL>

---

Without CTE :

SELECT E.* 
FROM Equip_price E
INNER JOIN (
              SELECT ep.equipmentID, min(ep.price) minprice
               FROM Equip_price ep
              GROUP BY ep.equipmentID
            ) aggr
ON (E.equipmentID = aggr.equipmentID AND E.price = aggr.minprice)

OR

SELECT ep.id,
       ep.equipmentID,
       ep.price
 FROM (
       SELECT ep.*,
              row_number() OVER(PARTITION BY ep.equipmentID ORDER BY ep.price)
                AS row_identifier
       FROM Equip_price ep
      ) ep
 WHERE ep.row_identifier = 1;

---

Related Topics