Find lowest value for column and return all row data? (oracle sql)

Question

I am trying to return all columns for a row that has the minimum value for one column, example:

COL 1        COl 2         COL3

Steve        XF3           17
Steve        XF4           2
Steve        XF5           1 
Jason        XF6           16
Jason        XF7           2

I would like to return rows 3 and 5 and all of their respective columns, because COL3 has the lowest value FOR STEVE and JASON. Pretend column2 cannot be leveraged as it cannot.

Much appreciated for any help on this.

I can't paste into here but I tried applying min function but ran into issues because there are multiple steve and jason.

what happens when steve has 2 or more 1 rows or is col3 unique? — nbk, Feb 16 '23 at 15:19
Use RANK() if you want all the equal lowest values, use ROW_NUMBER() if you want an arbitrary one of them. — Serg, Feb 16 '23 at 15:29
In your post you said Col2 and col3 can't be leveraged so what is the criteria to consider the lowest? Perhaps you should edit your question and only supply applicable data only — Beefstu, Feb 16 '23 at 15:57

Beefstu · Answer 1 · 2023-02-16T20:38:11.517


CREATE TABLE mytable( col1, col2, col3) AS 
SELECT 'Steve',  'XF3',17 FROM DUAL UNION ALL 
SELECT 'Steve', 'XF4',  2 FROM DUAL UNION ALL 
SELECT 'Steve', 'XF5',  1 FROM DUAL UNION ALL 
SELECT 'Jason', 'XF6', 16 FROM DUAL UNION ALL 
SELECT 'Jason','XF7', 2 FROM DUAL 

select col1,
          col2,
          col3
from
       (
       SELECT col1, 
              col2,
              col3,
              min(col3) over (partition by col1) min_col3
       FROM   mytable
       )
where col3 = min_col3

COL1    COL2    COL3
Jason   XF7 2
Steve   XF5 1

WITH cte AS (
  SELECT
     col1, col2, col3,
         DENSE_RANK() OVER(PARTITION BY col1 ORDER BY col3) AS rnk
  FROM mytable 
)
SELECT 
     col1, col2, col3
FROM   cte 
WHERE  rnk=1


COL1    COL2 COL3
Jason   XF7  2
Steve   XF5  1

Find lowest value for column and return all row data? (oracle sql)

1 Answers1