SQL oracle Select condition in group

Question

Let's suppose I have a table with 3 columns:

ID | GroupId | DateCreatedOn |

I want to select the datas grouped by GroupId so:

select GroupId from tableName group by GroupId;

But what if I want to execute another select on each group? let's suppose now that I want the last created row (DateCreatedOn) of each group?

Also, I would like to retrieve ALL the columns and not only the GroupId.

Im kind of lost because I only have GroupId available.

Please provide some explanation and not only the correct query.

General GROUP BY tip, a selected column should either be argument to a set function (e.g. AVG), or be listed in the group by clause. (Seldom both.) — jarlh, Mar 18 '16 at 13:16

score 1 · Accepted Answer · answered Mar 18 '16 at 13:17

1

You can use ROW_NUMBER for this:

SELECT ID, GroupId, DateCreatedOn
FROM (
  SELECT ID, GroupId, DateCreatedOn,
         ROW_NUMBER() OVER (PARTITION BY GroupId 
                            ORDER BY DateCreatedOn DESC) AS rn
  FROM mytable) t
WHERE t.rn = 1

rn field is equal to 1 for the record having the most recent DateCreatedOn value within each GroupId partition.

answered Mar 18 '16 at 13:17

Giorgos Betsos

71,379
9
63
98

1

I deleted my comment because I made a mistake on my query. Your query leads me to http://stackoverflow.com/questions/10477085/oracle-partition-by-and-row-number-keyword where I could find a great explanation of Partition by. Thanks – Neb Mar 18 '16 at 13:47

score 1 · Answer 2 · answered Mar 18 '16 at 13:42

You can get the values with the maximum of another column using KEEP ( DENSE_RANK [FIRST|LAST] ORDER BY ... ) in the aggregation:

SELECT   GroupID,
         MAX( ID ) KEEP ( DENSE_RANK LAST ORDER BY DateCreatedOn ) AS id
         MAX( DateCreatedOn ) AS DateCreatedOn
FROM     table_name
GROUP BY GroupId

You can also do it using ROW_NUMBER():

SELECT ID,
       GroupID,
       DateCreatedOn
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY GroupID
                             ORDER BY DateCreatedOn DESC, ID DESC ) AS RN
  FROM   table_name t
)
WHERE  RN = 1

(ID DESC is added to the ORDER BY to get the maximum ID for the latest DateCreatedOn to give the same result as the first query; if you don't have a deterministic order then you are likely to get whichever row the database produces first and the result can be non-deterministic)

SQL oracle Select condition in group

2 Answers2