CODEWITHSUNDEEP
sqloracle-database
erik.c
erik.c

Reputation: 1362

select rows with max value from group

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

Upvotes: 1

Views: 2628

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 2

MT0
MT0

Reputation: 167972

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 |

Upvotes: 3

Tenzin
Tenzin

Reputation: 2505

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;

Upvotes: 0

Khazratbek
Khazratbek

Reputation: 1656

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

Upvotes: -1

AntDC
AntDC

Reputation: 1917

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

Upvotes: 0

Related Questions