6

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
Karunakar
  • 2,209
  • 4
  • 15
  • 20

1 Answers1

3

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

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
  • sry ..it is not giving expected result – Karunakar Sep 21 '13 at 08:51
  • @user2795017 Why do you say it isn't giving the expected results? See [this fiddle](http://sqlfiddle.com/#!2/14597/1) to see it working. Ah. what is the data type for value? – Filipe Silva Sep 21 '13 at 09:02
  • hi..see my expected output and cross verify with ur output...i have gone through ur output..it is not matching – Karunakar Sep 21 '13 at 09:11
  • @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? – Filipe Silva Sep 21 '13 at 09:15
  • 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.... – Karunakar Sep 21 '13 at 09:20
  • 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... – Karunakar Sep 21 '13 at 09:37
  • i have updated input and expected output...here, i have to get its corresponding y value also – Karunakar Sep 21 '13 at 09:42
  • @user2795017. What are you comparing Y to to get the min_max of .05,.06 on f1? – Filipe Silva Sep 21 '13 at 09:46
  • no comparison ...just get the corresponding y value for the previously computed x from table 2 – Karunakar Sep 21 '13 at 09:49
  • @user2795017. Can you be a little more explicit with an example of what that x you talk is? – Filipe Silva Sep 21 '13 at 09:53
  • we are getting x value from table 2...i want its corresponding y value also....see input i have updated – Karunakar Sep 21 '13 at 09:58
  • @user2795017. Then how do you get .05,.06 for your y.min_max and y.max_min if value is .04? – Filipe Silva Sep 21 '13 at 10:00
  • @user2795017. see this [sqlfiddle](http://sqlfiddle.com/#!2/48d08/3) to see if it is what you want – Filipe Silva Sep 21 '13 at 10:03
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/37778/discussion-between-filipe-silva-and-user2795017) – Filipe Silva Sep 21 '13 at 10:08