CODEWITHSUNDEEP
sqloracle-database
Jens
Jens

Reputation: 11

min value from two columns (Oracle SQL)

Searching for a while now, but haven't found a suitable answer to my problem.

Explanation:

ID | Year |  Factor1 | Number1 | Number2
1  | 2010 |   213    |    1     |   1     
2  | 2010 |   213    |    1     |   2    
3  | 2010 |   214    |    2     |   1    
4  | 2010 |   214    |    2     |   2    
6  | 2010 |   210    |    3     |   1    
7  | 2010 |   210    |    3     |   2   
8  | 2011 |   250    |    3     |   5 
5  | 2012 |   214    |    2     |   4

EDIT: Forgot Something, corrected that in the above table. I need 2 combinations: year and factor1 only once, then min(number1) and last min(number2). For example above I would need IDs 1,3,5,6,8 (sorry they are mixed for better readability of the other values).

Anyone any idea how to realize that?

Upvotes: 0

Views: 13532

Answers (3)

Jens
Jens

Reputation: 11

Got the code now from another forum, works fine for me:

SELECT 
  tab.*
FROM
  t33 tab
  INNER JOIN (
    SELECT
      m1.y,
      m1.factor1,
      m1.min_1,
      m2.min_2
    FROM
      (
        SELECT
          y,
          factor1,
          MIN(number1) AS min_1
        FROM
          t33 tab
        GROUP BY
          y,
          factor1
      ) m1
      INNER JOIN (
        SELECT 
              y,
              factor1,
              number1,
              MIN(number2) AS min_2
            FROM 
              t33
            GROUP BY
              y,
              factor1,
              number1
        ) m2
      ON m1.y = m2.y
      AND m1.factor1 = m2.factor1
      AND m1.min_1 = m2.number1
    ) sel
      ON tab.y = sel.y
      AND tab.factor1 = sel.factor1
      AND tab.number1 = sel.min_1
      AND tab.number2 = sel.min_2

Upvotes: 1

Michael Broughton
Michael Broughton

Reputation: 4055

OK, so something like this?

delete from mb_test
where ID not in
   ( select id
     from mb_test mbt
         ,(select year, min(number1) as min1, min(number2) as min2
           from mb_test
           group by year) mb_mins
     where mbt.year = mb_mins.year
     and   (mbt.number1 = mb_mins.min1 OR mbt.number2 = mb_mins.min2)
   )

Upvotes: 0

Mursa Catalin
Mursa Catalin

Reputation: 1449

SELECT id , year, number1, number2 
FROM @table A
WHERE number1 IN (SELECT MIN(number1) FROM @table WHERE year = A.year)
OR number2 IN (SELECT MIN(number2) FROM @table WHERE year = A.year)

where @table is your table

Upvotes: 2

Related Questions