Oracle SQL query to group consecutive records
analytics, oracle, sql
Solution
Try this query:
SELECT line_no,
amount,
narration,
SUM( x ) OVER ( ORDER BY line_no
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as calc_group
FROM (
SELECT t.*,
CASE lag( narration ) OVER (order by line_no )
WHEN narration THEN 0
ELSE 1 END x
FROM test t
)
ORDER BY line_no
demo --> http://www.sqlfiddle.com/#!4/6d7aa/9
Problem
I've imported data ("Amount" and "Narration") from a spreadsheet into a table and need help with a query to group consecutive records according to their "Narration", for example: Expected output: ``` line_no amount narration calc_group <-Not part of table ---------------------------------------- 1 10 Reason 1 1 2 -10 Reason 1 1 3 5 Reason 2 2 4 5 Reason 2 2 5 -10 Reason 2 2 6 -8 Reason 1 3 7 8 Reason 1 3 8 11 Reason 1 3 9 99 Reason 3 4 10 -99 Reason 3 4 ``` I've tried some analytical functions: ``` select line_no, amount, narration, first_value (line_no) over (partition by narration order by line_no) "calc_group" from test order by line_no ``` But that does not work because the Narration of line 6 to 8 is the same as line 1 and 2. ``` line_no amount narration calc_group ---------------------------------------- 1 10 Reason 1 1 2 -10 Reason 1 1 3 5 Reason 2 3 4 5 Reason 2 3 5 -10 Reason 2 3 6 -8 Reason 1 1 7 8 Reason 1 1 8 11 Reason 1 1 9 99 Reason 3 4 10 -99 Reason 3 4 ``` UPDATE I've managed to do it using lag analytical function and sequences, not very elegant but it works. There should be a better way, please comment! ``` create or replace function get_next_test_seq return number as begin return test_seq.nextval; end get_next_test_seq; create or replace function get_curr_test_seq return number as begin return test_seq.currval; end get_curr_test_seq; update test set group_no = (with cte1 as (select line_no, amount, narration, lag (narration) over (order by line_no) prev_narration, group_no from test order by line_no), cte2 as (select line_no, amount, narration, group_no, case when prev_narration is null or prev_narration <> narration then get_next_test_seq else get_curr_test_seq end new_group_no from cte1) select new_group_no from cte2 where cte2.line_no = test.line_no); ``` UPDATE 2 I'm satisfied with the better accepted answer. Thanks kordiko!