query efficiency - select the 2 latest “group/batc

Question

We have a tested a quite interesting SQL query. Unfortunately, It turned out that this query runs a little bit slow - O(n2) - and we are looking for a optimized solution or maybe also a totally different one?

Goal:

We would like to get for:
 - some customers ("record_customer_id"), e.g. ID 5
     - the latest 2 "record_init_proc_id" 
       - for every "record_inventory_id"

http://www.sqlfiddle.com/#!9/07e5d/4

The query works fine and shows the correct results but uses at least two full table scans which is of course horrible when having too many rows to scan.

Is it possible to (a) select first all record_customer_id(s) in a specific date range, saving the results of that query and then (b) run the working query in SQL fiddle over these saved results?

Or is there maybe a totally different approach out there which has a great performance?

Any help is greatly appreciated!

Answer 1

I give a generic O(N) solution in my Groupwise-Max blog