Oracle SQL Subqueries

A commonly encountered problem to query a min/max per group or some other criteria – can be solved with subqueries

“A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.” reference 1 reference 2

select id, startdate 
from employee, (SELECT grade, min(startdate) as minentrydate
  from employee
  GROUP BY rank) subquery
where subquery.minentrydate = employee.startdate 
and employee.grade = subquery.grade;