Oracle: Getting Maximum Value of a Group

Oracle: getting maximum value of a group?

I'd tend to use analytic functions

SELECT monitor_id,
       host_name,
       calibration_date,
       value
  FROM (SELECT b.monitor_id,
               b.host_name,
               a.calibration_date,
               a.value,
               rank() over (partition by b.monitor_id order by a.calibration_date desc) rnk
          FROM table_name a,
               table_name2 b
         WHERE a.some_key = b.some_key)
 WHERE rnk = 1

You could also use correlated subqueries though that will be less efficient

SELECT monitor_id,
       calibration_date,
       value
  FROM table_name a
 WHERE a.calibration_date = (SELECT MAX(b.calibration_date)
                               FROM table_name b
                              WHERE a.monitor_id = b.monitor_id)

Oracle SQL get row with MAX value for each group in a set of grouped results

You can use CTEs

WITH CTE0 AS 
(
  SELECT 
       REV_USAGE_DATA.DDATE, 
       REV_USAGE_DATA.SEGMENT, 
       COUNT(*) AS Freq
  FROM CADA_PERMSISDN_DASH REV_USAGE_DATA
  GROUP BY 
       REV_USAGE_DATA.DDATE,
       REV_USAGE_DATA.SEGMENT
)
SELECT 
  DDATE,
  SEGMENT, 
  FREQ
FROM CTE0 
WHERE (DDATE, SEGMENT, FREQ) IN (
  SELECT DDATE, MAX(SEGMENT), MAX(FREQ)
  FROM CTE0 
  GROUP BY DDATE
)

SQL Query: Return Max value record of a Group

use row_number() window function

select * from
 ( select *,
  row_number()over(partition by s_name order by MARK_VALUE desc) rn
 from table_name
) t where t.rn=1

or you can use corelated subquery

select t1.* from table_name t1
  where t.MARK_VALUE=(select max(MARK_VALUE) from table_name t2 where t2.S_NAME=t1.S_NAME)

select rows with max value from group

You can use the RANK (or DENSE_RANK) analytic functions to find the maximum value(s) within a group:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( id, col1, col2, col3, col4, col5, date_col ) AS
          SELECT  1, 1, 1, 1, 1, 1, DATE '2015-11-13' FROM DUAL
UNION ALL SELECT  2, 1, 1, 1, 1, 2, DATE '2015-11-12' FROM DUAL
UNION ALL SELECT  3, 1, 1, 1, 1, 3, DATE '2015-11-11' FROM DUAL
UNION ALL SELECT  4, 1, 1, 1, 1, 4, DATE '2015-11-13' FROM DUAL
UNION ALL SELECT  5, 1, 1, 1, 1, 5, DATE '2015-11-12' FROM DUAL
UNION ALL SELECT  5, 1, 1, 1, 1, 5, DATE '2015-11-12' FROM DUAL
UNION ALL SELECT  6, 1, 1, 1, 2, 1, DATE '2015-11-12' FROM DUAL
UNION ALL SELECT  7, 1, 1, 1, 2, 2, DATE '2015-11-13' FROM DUAL
UNION ALL SELECT  8, 1, 1, 1, 2, 3, DATE '2015-11-11' FROM DUAL
UNION ALL SELECT  9, 1, 1, 1, 2, 4, DATE '2015-11-12' FROM DUAL
UNION ALL SELECT 10, 1, 1, 1, 2, 5, DATE '2015-11-13' FROM DUAL

Query 1:

SELECT *
FROM   (
  SELECT t.*,
         RANK() OVER ( PARTITION BY col1, col2, col3, col4 ORDER BY date_col DESC ) AS rnk
  FROM   table_name t
)
WHERE  rnk = 1

Results:

| ID | COL1 | COL2 | COL3 | COL4 | COL5 |                   DATE_COL | RNK |
|----|------|------|------|------|------|----------------------------|-----|
|  1 |    1 |    1 |    1 |    1 |    1 | November, 13 2015 00:00:00 |   1 |
|  4 |    1 |    1 |    1 |    1 |    4 | November, 13 2015 00:00:00 |   1 |
|  7 |    1 |    1 |    1 |    2 |    2 | November, 13 2015 00:00:00 |   1 |
| 10 |    1 |    1 |    1 |    2 |    5 | November, 13 2015 00:00:00 |   1 |

Get maximum value of grouped by departments using SQL

If you need only 1 row from your aggregated query you can use subquery to get it.

SQL> select * 
2 from (
3       select dept,SUM(salary) sum_salary 
4       from org 
5       group by dept
6       order by sum_salary desc
7      )
8 where rownum <= 1

DEPT       SUM_SALARY
---------- ---------- 
  HW       113000

---

Related Topics