select rows with max value from group

Question

I have a problem with retrieving of a row with max value of a big group in oracle db.

my table looks like something like this:

id, col1, col2, col3, col4, col5, date_col

The group would consist of 4 columns col1, col2, col3, col4, so members mof the group should be equal on these fields, and from each group I need the rows (id is enough) with max date_col value (there can be several with same date).

Is it should be solved somehow with group by or probably there is a better approach?

Thanks for tips!

Cheers

Answer 1

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 |

Answer 2

I think you need conditional aggregation with row_number():

select t.*
from (select t.*,
             row_number() over (partition by col1, col2, col3, col4 order by date_col desc) as seqnum
      from t
     ) t
where seqnum = 1;

If you want all rows with the maximum value, then use dense_rank() or rank() instead.

You can also use keep to get the value of col5 using aggregation:

select col1, col2, col3, col4,
       max(col5) keep (dense_rank first order by date_col desc) as col5,
       max(date_col) as date_col
from t
group by col1, col2, col3, col4;

However, this only returns one value.

Answer 3

Is this what you are looking for..........

SELECT col1, col2, col3, col4, MAX(date_col), Count(id)
FROM YOUR_TABLE
WHERE 1=1
  AND any other condition
GROUP BY
  col1, col2, col3, col4

Answer 4

How about:

SELECT MAX(ColumnName) FROM Dual;

Edit:
I think you have to work with views to get what you would like.
I have no oppertunity to test this code, but I think you need something like this:

-- ViewA
SELECT  NULL Id, Col1, Col2, Col3, Col4, Col5, MAX(DateCol) 
FROM    TableA 
GROUP BY Col1, Col2, Col3, Col4, Col5

-- ViewB
SELECT  Id, Col1, Col2, Col3, Col4, Col5, DateCol 
FROM    TableA 

-- ViewC
SELECT  NVL(A.Id, B.Id) Id,
    A.Col1, 
        A.Col2, 
        A.Col3, 
        A.Col4, 
        A.Col5,
        A.DateCol
FROM    ViewA A, ViewB B 
AND     A.Col1 = B.Col1
AND A.Col2 = B.Col2 
AND A.Col3 = B.Col3
AND     A.Col4 = B.Col4; 

Answer 5

select T.id
  from table_name T
 where date_col = (select max(date_col) 
                     from table_name 
                    where col1 = T.col1)
group by col1;

Try out this solution