Second highest value among column values of same row in SQL

nth highest salary in db2
nth highest salary in sql
how to find max salary in sql without using max function
how to find second highest salary in sql using subquery
how to get third max value in sql
second best sql
sql select max and second max
postgresql select second highest value

Sample data:

id score1 score2 score3 score4
1  10     05      30    50
2  05     15      10    00
3  25     10      05    15

Expected result set:

id col_value
1    30
2    10
3    15

Consider the following, which generalizes yet more easily still:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(id INT NOT NULL
,score_no INT NOT NULL
,score INT NOT NULL
,PRIMARY KEY(id,score_no)
);

INSERT INTO my_table VALUES
(1, 1 ,10),
(1 ,2 ,05),
(1 ,3 ,30),
(1 ,4 ,50),
(2 ,1 ,05),
(2 ,2 ,15),
(2 ,3 ,10),
(2 ,4 ,00),
(3 ,1 ,25),
(3 ,2 ,10),
(3 ,3 ,05),
(3 ,4 ,15);

SELECT id
     , score_no
     , score 
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev=id THEN @i:=@i+1 ELSE @i:=1 END rank
            , @prev:=id 
         FROM my_table x
            , (SELECT @prev:=null,@i:=0) vars 
        ORDER 
           BY id
            , score DESC
            , score_no 
     ) a
 WHERE rank = 2;
 +----+----------+-------+
 | id | score_no | score |
 +----+----------+-------+
 |  1 |        3 |    30 |
 |  2 |        3 |    10 |
 |  3 |        4 |    15 |
 +----+----------+-------+

In the event of tied scores, this solution picks the one with the lower 'score_no'.

Get second highest value in a table, To get the second highest distinct value in the table you can use And it will give you 10th to 20th rows with all required columns. Or, if you use recent version of SQLServer (>= 2012), the lag function. you have duplicates, you might want to group by the value entry so that you only get distinct values. If your task is to return the maximum value contained on a row from n columns, you may want to program defensively, anticipating that some new column(s) might be added. See why Arthur Fuller says

Use a CASE expression to tell which score to omit in your GREATEST() call.

SELECT id,
    CASE GREATEST(score1, score2, score3, score4)
        WHEN score1 THEN GREATEST(score2, score3, score4)
        WHEN score2 THEN GREATEST(score1, score3, score4)
        WHEN score3 THEN GREATEST(score1, score2, score4)
        ELSE GREATEST(score1, score2, score3)
    END AS col_value
FROM your_table ;

This solution generalizes easily to any number of columns.


and a variation without CASE, using both GREATEST() and LEAST():

SELECT id,
    LEAST(
        GREATEST(score1, score2, score3),
        GREATEST(score2, score3, score4),
        GREATEST(score3, score4, score1),
        GREATEST(score4, score1, score2)
    ) AS col_value
FROM your_table ;

Query to find 2nd largest value in a column in Table, This is a nested sub query which is a generic SQL query to print the Nth largest value in column. For each record processed by outer query, inner query will be  When we need to compare values of more columns we would have to rewrite the function or create a new one, because in SQL Server we can't create a function with a dynamic number of parameters. Conclusion. In SQL Server we can find the maximum or minimum value from different columns of the same data type using different methods.

Assuming no ties, you can use a big case expression:

select t.*,
       (case when score1 > score2 and score1 > score3 and score1 < score 4 then score1
             when score1 > score2 and score1 < score3 and score1 > score 4 then score1
             when score1 < score2 and score1 > score3 and score1 > score 4 then score1
             when score2 > score1 and score2 > score3 and score2 < score 4 then score2
             when score2 > score1 and score2 < score3 and score2 > score 4 then score2
             when score2 < score1 and score2 > score3 and score2 > score 4 then score2
             . . .
        end) as second_score          
from t;

In general, though, this type of question suggests a problem with the data structure. I suspect that you should really have a table with one row per id and score (and perhaps a score number). This is usually easier to manipulate in SQL.

Find MAX value from multiple columns in a SQL Server table, Find MAX value from multiple columns in a SQL Server table the maximum or minimum value from different columns in a table of the same data type. Our task is to find the last update date for each row using the three dates and the first query, 11% for the second, 16% for the third and 66% for the forth. IN SQL Server using Common Table Expression or CTE, we can find the second highest salary: WITH T AS ( SELECT * DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk FROM Employees ) SELECT Name FROM T WHERE Rnk=2;

Using UNPIVOT try this query

    CREATE TABLE #my_table
(id INT NOT NULL, score1 INT NOT NULL, score2 INT NOT NULL, score3 INT NOT NULL, score4 INT NOT NULL) 

INSERT INTO #my_table VALUES(1,  10,     05,      30,    50)
INSERT INTO #my_table VALUES(2,  05,     15,      10,    00)
INSERT INTO #my_table VALUES(3,  25,     10,      05,    15)

;WITH getHighestValue as (
SELECT id, Scores, ScoreText, ROW_NUMBER() OVER(PARTITION BY id ORDER BY Scores DESC) AS Ranks
FROM #my_table
UNPIVOT(
Scores for ScoreText in (score1,score2,score3,score4)
) unpiv
)
SELECT id, Scores as col_value 
FROM getHighestValue
WHERE Ranks = 2

Result:

Second Highest Salary in MySQL and SQL Server, Write a SQL query to get the second highest salary from the Employee table. contain duplicate salary, In this example there is no such record, so I have not used Again we did the same thing but this time we sort the result set on ascending to see the data from a large table, just to understand columns and data inside it. If the comparison gives a True value, a row is added to the result table. You can guarantee that a subquery will return a single value if you include an aggregate function in it. Aggregate functions always return a single value. Of course, this way of returning a single value is helpful only if you want the result of an aggregate function.

How to find the second largest value in a table using SQL, How many rows can be inserted at a time in an SQL database? 362 Views · How do you return a SELECT statement if the result is between two values in SQL? Can you use WHERE and HAVING in the same SQL query? Now the second largest value of Salary column is 200, so your query should return 200, otherwise​  You asked: How do you find the second largest value in a table using SQL? Thanks for the A2A Lots of working answers here, but I prefer this approach because it finds and excludes the largest value which only leaves the second highest remaining.

Finding second highest value in a column, Hi, I want to find out the second highest value in a column. The column has only numeric values. a good manner because rownum is generated by oracle server for tables rows . if in ur column have 2 same value then use distinct 1998-2020 Toolbox is among the trademarks of Ziff Davis, LLC and may not be used  I am having three column named mark1,mark2,mark3 (for eg:80,86,69) , i need to select the maximum values from these three values and put them in a new column what is the sql query for selecting maximum values among multiple columns please hellp me..

MySQL Select the nth Highest Record in a Database Table, In this tutorial, you will learn how to select the nth highest record in a table using Fortunately, MySQL provides us with the LIMIT clause that constrains the number of rows in the returned result set. You can achieve the same result using the first technique to get the second Next MySQL Reset Auto Increment Values  Getting the first & Second highest number record from a table How to get the second highest mark of a class? We will use two sql commands limit and order by along with the select command to get the second highest record in a table. We will use our student table where each student mark is stored in a field.

Comments
  • And what do you do if there are ties?
  • GREATEST(score1,score2,...) returns the first highest value. I am looking for second highest value
  • Alternatively to @GordonLinoff's answer, you could pivot this to rows and apply a ranking function.
  • @satyasainath That clarification should be in the question, not a comment.
  • @satyasainath If you have scope and time for changing table structure, do consider Strawberry's solution. You really need to normalize your tables. Your current table structure is not scalable at all.
  • Thank you very much for this logic. it will help
  • Only foreseeable issue would be if there are duplicate scores that are also the highest. sqlfiddle.com/#!9/8feb44/2 See ID 4 - expected 2 received 3
  • The OP never answered Gordon's question about how duplicates should be handled.
  • Besides, it's not the only forseeable issue :-(
  • @Barmar nice idea!. I added a variation using your theme ;)
  • I have 22 columns like this. I just shared as a sample columns here. thanks for your quick response though. is there any other chance to get this second highest value.? i can easily get first highest value using GREATEST.
  • Why do you have 22 columns like this? This sounds like a normalization problem.
  • @satyasainath . . . It is only possible to answer the question that you actually ask. This question is clearly about 4 columns. You should ask another question if that is not what you really intend. If you change this question, you'll invalidate my answer which will draw downvotes.
  • My answer is generalizes more easily.