in core oracle sql or sql server there are analytic functions such as lag and lead to use for the specific scenario. Netsuite has Dense_rank and Rank functions ... at this time i haven't explored how those could be used for the type of query you are attempting to build