SELECT min max and max min value from a part of a table in MySQL

Question

I want to select min max and max min values for each value in table1 from another table2. sample input

table2
name, value,y
f1, .01,.04
f1,.02,.05
f1,.05,.06
f1,.45,.07
f2,.03,.09
f2,.05,.02

table1
name, value
f1, .04
f2,.04

expected output
table3
name, value,min_max-value,max_min-value,y(min_max-value),y(max_min-value)
f1, .04,.02,.05,.05,.06
f2,.04,.03,.05,.09,.02

Hodess

Answer 1

You can get MAX and MIN values from t2 with the following query:

SELECT
    t1.name,
    t1.value,
    (SELECT min(value) FROM table2 t2 WHERE t2.name = t1.name),
    (SELECT MAX(value) FROM table2 t2 WHERE t2.name = t1.name)
FROM table1 t1

EDIT: To get the values closer to the t1.value and corresponding y value you could do:

SELECT 
    t1.NAME, 
    t1.value, 
    (SELECT MAX(t2.value)
      FROM table2 t2
      WHERE t2.NAME = t1.NAME AND t2.value < t1.value) AS maxmin, 
    (SELECT MIN(t2.value)
      FROM table2 t2
      WHERE t2.NAME = t1.NAME AND t2.value > t1.value) AS minmax, 
    (SELECT t2.y
      FROM table2 t2
      WHERE t2.NAME = t1.NAME AND t2.value = (
            SELECT MAX(t3.value)
            FROM table2 t3
            WHERE t3.NAME = t1.NAME AND t3.value < t1.value
            )) AS ymaxmin, 
    (SELECT t2.y
      FROM table2 t2
      WHERE t2.NAME = t1.NAME AND t2.value = (
            SELECT MIN(t3.value)
            FROM table2 t3
            WHERE t3.NAME = t1.NAME AND t3.value > t1.value
            )) AS yminmax
FROM table1 t1

see it in this fiddle

Atomism

sry ..it is not giving expected result – Hodess

@user2795017 Why do you say it isn't giving the expected results? See this fiddle to see it working. Ah. what is the data type for value? – Atomism

hi..see my expected output and cross verify with ur output...i have gone through ur output..it is not matching – Hodess

@user2795017. Your output says that you want f1, .04, .02, .05, but you says that you want the min on the second column, but on the first line of t2 you have .01, wich is smaller than .02, so this wouldn't happen ever. The same for the max of f2, it is .45, not .05 wich is from f2. Are you sure that your expected result is right? – Atomism

ya..i am sure..i want my expected result...here scenario is like this for value=x we have to find maximum value among values which are less than x in the same name...which is our min_max-value.... – Hodess

ya...it is giving but with small changes like adding min and max functions in inner queries....i am facing one more problem...i will update my expected input and output ....shortly... – Hodess

i have updated input and expected output...here, i have to get its corresponding y value also – Hodess

@user2795017. What are you comparing Y to to get the min_max of .05,.06 on f1? – Atomism

no comparison ...just get the corresponding y value for the previously computed x from table 2 – Hodess

@user2795017. Can you be a little more explicit with an example of what that x you talk is? – Atomism

we are getting x value from table 2...i want its corresponding y value also....see input i have updated – Hodess

@user2795017. Then how do you get .05,.06 for your y.min_max and y.max_min if value is .04? – Atomism

@user2795017. see this sqlfiddle to see if it is what you want – Atomism

let us continue this discussion in chat – Atomism