title: Oracle MAX function for SELECT statement against two tables
body: <p>Hi there Oracle folks ,</p>
  
  <p>I have two tables called book_items and  book_items_hist. </p>
  
  <p>The join between the two tables is book_item_id.</p>
  
  <p>In book_items, book_item_id is unique therefore there is only one book_item_id per record in the table.</p>
  
  <p>In book_items_hist,  book_item_id is not unique, there are multiple book_item_id records in the table, for background reference, this is an history audit table.</p>
  
  <p>I am trying to figure out how to use the MAX function to get me the latest record from book_items_hist b, the WHERE clause is on the b.book_timestamp.</p>
  
  <p>However, the SQL below returns multiple rows from book_items_hist table and I am looking for a distinct results set.</p>
  
  <pre><code>Select a.book_item_id, a.book_sale_item_status, a.book_name  b.book_timestamp
  from book_items  a
  join  book_items_hist b  on a.book_item_id = b.book_item_id
  group by a.book_item_id, a.book_sale_item_status, a.book_name, b.book_timestamp
  having max(b.book_timestamp)  &lt; (systimestamp - interval '10' minute)
  </code></pre>
  
  <p>Would appreciate some help along with an explanation/breakdown so that I can learn from this.</p>
  
  <p>Thank You.</p>