How to choose the n-th smallest, and an array with the condition - if in the adjacent column 1, then the value is taken into the array, if 0, then no

  • If you just need to select from the sample to show the smallest value across the field, then use the aggregate function MIN() - MaximPro
  • No, I need not the first minimum, but for example, 7e - Elena Katz
  • one
    Formulate the question more clearly! The success of solving your problem depends on the right question : MaximPro
  • there is an array, for example: 1,2,3,4,5 I need to find the 3rd lowest value in a row. In this case, it will be 3 - Elena Katz
  • 3
    and if in the array 1, 1, 2, 3, 4, 5 - will be 3 or 2 in response? - Denis

2 answers 2

nth least For MySQL, use the LIMIT . For other DBMS, something similar

 SELECT val FROM myTable GROUP BY val ORDER BY val LIMIT 1 OFFSET n;

and an array with the condition - if the adjacent column is 1, then the value is taken into the array, if 0, then there is no

 SELECT val FROM myTable WHERE test_col = 1;

    The row_number function (or its analogs) can help you. Example for sql-server:

     WITH DT AS ( SELECT ROW_NUMBER() OVER(ORDER BY ColumnName) AS Num, * FROM TableName ) SELECT * FROM dt WHERE Num = 7

    Or another option (works, starting with sql-server 2012):

     SELECT * FROM TableName ORDER BY ColumnName OFFSET 6 ROWS FETCH NEXT 1 ROWS ONLY