How to get the minimum of one column and get another column without grouping?-CodePudding

I'm trying to get a record from a table that has the minimum value of a specific column, but I want the rest of the data in that record without grouping one of the columns.

This is sample data:

I want a query that will return:

January, 24, 0, 3
February, 12, 0, 1

CodePudding user response：

One option is to use analytic function which "ranks" data; then fetch rows that rank as the highest. Something like this:

Sample data:

SQL> with test (month, day, c_initial, ending) as
  2    (select 'jan', 24, 0, 3 from dual union all
  3     select 'jan', 24, 1, 6 from dual union all
  4     select 'jan', 24, 2, 2 from dual union all
  5     select 'feb', 12, 0, 1 from dual union all
  6     select 'feb', 12, 1, 6 from dual union all
  7     select 'feb', 12, 2, 5 from dual
  8    ),

Query begins here:

  9  temp as
 10    (select t.*,
 11       row_number() over (partition by month, day order by c_initial) rn
 12     from test t
 13    )
 14  select month, day, c_initial, ending
 15  from temp
 16  where rn = 1;

MON        DAY  C_INITIAL     ENDING
--- ---------- ---------- ----------
feb         12          0          1
jan         24          0          3

SQL>

If there are ties, then consider RANK instead.

Also, initial is invalid column name so I renamed it to c_initial.

CodePudding user response：

@Littlefoot gave probably the only reasonable answer. However, if your table is very large and performance is an issue, this version might be more efficient:

select    month, 
          day, 
          min(c_initial) keep ( dense_rank first order by c_initial asc ) c_initial,
          min(ending) keep ( dense_rank first order by c_initial asc ) ending
from      your_table
group by  month, day;

The min() keep tells Oracle you just want the value for which ever row in each group has the lowest value for c_initial. So, it can discard the values it knows you're not interested in, rather than keeping them around, sorting them, and then giving you the first one.

Incidentally, what tells Oracle to return the data for the row having this lowest c_initial value is the dense_rank first order by c_initial asc clause and NOT the min() function. Using a max() function would give the exact same answer. I use min() is just a style thing -- it reminds me that I'm looking for the first row in the group.