How to select the row with the max/min number in each group after GROUP BY using Oracle

The scenario is described as below: Assume an insurance company maintains two tables: Service and Policy. The Service table has two attributes -  serviceID and name. The Policy table has three attributes - policyID, servideID, customerName and year. Now let's create these two tables. CREATE TABLE Service( serviceID CHAR(2) PRIMARY KEY, name VARCHAR2(50) NOT NULL ); CREATE TABLE Policy( policyID CHAR(2) PRIMARY KEY, serviceID CHAR(2) NOT NULL, customerName VARCHAR2(50) NOT NULL, year NUMBER(4) NOT NULL, FOREIGN KEY (serviceID) REFERENCES Service(serviceID)  ); Insert rows into Service table,  INSERT INTO Service VALUES('01', 'Home Insurance'); INSERT INTO Service VALUES('02', 'Auto Insurance'); INSERT INTO Service VALUES('03', 'Personal Insurance'); Insert rows into Customer table, INSERT INTO Policy VALUES('01', '01', 'David', 2012); INSERT INTO Policy VALUES('02', '01', 'John', 2012); INSERT INTO Policy VALUES('03', '01', 'Alex', 2012); INSERT INTO Policy VALUES('04', '01', 'Bill', 2011); INSERT INTO Policy VALUES('05', '02', 'William', 2011); INSERT INTO Policy VALUES('06', '02', 'Jack', 2011); INSERT INTO Policy VALUES('07', '02', 'Steve', 2011); INSERT INTO Policy VALUES('08', '02', 'Joe', 2010); INSERT INTO Policy VALUES('09', '03', 'Jane', 2009); INSERT INTO Policy VALUES('10', '03', 'Sam', 2009); INSERT INTO Policy VALUES('11', '03', 'Jim', 2009); INSERT INTO Policy VALUES('12', '03', 'Tim', 2010); Now let's start to write the query that returns, for each Service department, the year in which the largest number of policy signed. You need to return the serviceID, the year and the number of the policies for that service and for that year.  The first thing that come to our mind is to write query like below.  ------------Q1---------------- SELECT serviceID, year, COUNT(*) AS Num_Of_Policy  FROM Policy GROUP BY serviceID, year ORDER BY serviceID, year; This query returns T1:                          The next thing we need to do is to select the year with the largest number of policies based on the first query.  ------------Q2---------------- SELECT serviceID, MAX(Num_Of_Policy) AS Max_Num_Of_Policy FROM  (SELECT serviceID, year, COUNT(*) AS Num_Of_Policy  FROM Policy GROUP BY serviceID, year ORDER BY serviceID, year) GROUP BY serviceID; This query will return T2. It's tempting to write the following two queries, but neither of them works.  1)  SELECT serviceID, year, MAX(Num_Of_Policy) AS Max_Num_Of_Policy FROM  (SELECT serviceID, year, COUNT(*) AS Num_Of_Policy  FROM Policy GROUP BY serviceID, year ORDER BY serviceID, year) GROUP BY serviceID, year ORDER BY serviceID, year; , which returns T1 2)  SELECT serviceID, year, MAX(Num_Of_Policy) AS Max_Num_Of_Policy FROM  (SELECT serviceID, year, COUNT(*) AS Num_Of_Policy  FROM Policy GROUP BY serviceID, year ORDER BY serviceID, year) GROUP BY serviceID; , which is not in correct syntax. We are not finished yet at this point, because we still need one more field, the year. We achieve this by joining two T1 and T2. Note the joining field are serviceID and Num_Of_Policy.  SELECT T2.serviceID, T1.year, T2. Max_Num_Of_Policy FROM  ----------------Q1------------------ (SELECT serviceID, year, COUNT(*) AS Num_Of_Policy  FROM Policy GROUP BY serviceID, year ORDER BY serviceID, year) T1,  ----------------Q2------------------ (SELECT serviceID, MAX(Num_Of_Policy) AS Max_Num_Of_Policy FROM  (SELECT serviceID, year, COUNT(*) AS Num_Of_Policy  FROM Policy GROUP BY serviceID, year ORDER BY serviceID, year) GROUP BY serviceID) T2 WHERE T1.serviceID=T2.serviceID AND T1.Num_Of_Policy=T2.Max_Num_Of_Policy; The final result is Conclusion The tricky thing about this query is that you need to add the field, year,  back to the final result, which is eliminated in Q2. We achieve this by performing Q1 again, and joining the two resulting table. As far as I know, there is no way to achieve this query within two steps. The join between T1 and T2 is necessary.