Share
Go down
avatar
Admin
Posts : 155
Join date : 2017-11-11
Age : 27
View user profilehttp://net-media.yoo7.com

How to get the full row of data based on max value?

on Sat Dec 16, 2017 10:06 pm
I have an Oracle table with a usage counter. I need to get the full row of data for each sensor with the max counter value?

For ALO I need data in row 2. For AMA I need data in row 10. For A11 I need data in row 9658. For MSP I need data in row 9659.

Any help would be greatly appreciated!

Thanks,

Dave

Answers
Code:
selcet * from (
  select a.*, row_number() over (partition by facility_id_n
      order by usage_counter_n desc) rn
  from your_tbl a)
where rn =1;

ROW_NUMBER won't return correct result if there are two rows with the same USAGE_COUNTER_N value per FACILITY_ID_N. For example:
Code:
SQL> WITH test
  2      AS (SELECT 9640 ID_n, 'ALO' sensor_id_c, 317 usage_counter_n FROM DUAL
  3          UNION
  4          SELECT 9641, 'ALO', 18 FROM DUAL
  5          UNION
  6          SELECT 9642, 'ALO', 0 FROM DUAL
  7          UNION
  8          SELECT 9659, 'MSP', 25 FROM DUAL --> MAX for MSP ...
  9          UNION
 10          SELECT 9660, 'MSP', 10 FROM DUAL
 11          UNION
 12          SELECT 1000, 'MSP', 25 FROM DUAL --> ... but this is also MAX for MSP
 13          )
 14  SELECT *
 15    FROM (SELECT a.*,
 16                ROW_NUMBER ()
 17                OVER (PARTITION BY sensor_id_c
 18                      ORDER BY usage_counter_n DESC) rn
 19            FROM test a)
 20  WHERE rn = 1;

      ID_N SEN USAGE_COUNTER_N        RN
---------- --- --------------- ----------
      9640 ALO            317          1
      1000 MSP              25          1

SQL>
RANK might be better:

SQL> l16
 16*                ROW_NUMBER ()
SQL> c/row_number/rank
 16*                rank ()
SQL> /

      ID_N SEN USAGE_COUNTER_N        RN
---------- --- --------------- ----------
      9640 ALO            317          1
      1000 MSP              25          1
      9659 MSP              25          1

SQL>
Or, using the oldfashioned way:

 14    SELECT *
 15      FROM test t
 16    WHERE t.usage_counter_n = (SELECT MAX (t1.usage_counter_n)
 17                                  FROM test t1
 18                                WHERE t1.sensor_id_c = t.sensor_id_c)
 19  ORDER BY sensor_id_c;

      ID_N SEN USAGE_COUNTER_N
---------- --- ---------------
      9640 ALO            317
      9659 MSP              25
      1000 MSP              25

SQL>
Back to top
Permissions in this forum:
You can reply to topics in this forum