Menu
I need help in writing a query in Oracle for the following data. The data is sorted by Person and Day fields.
Person Day Flag
------ --- ----
person1 day1 Y
person1 day2 Y
person1 day3 Y
person1 day4 N
person1 day5 N
person1 day6 Y
person1 day7 Y
person1 day8 Y
I need to have a Group_Number column that gets incremented whenever the Flag value changes. My result should look as below
Person Day Flag Group_Number
------ --- ---- ------------
person1 day1 Y 1
person1 day2 Y 1
person1 day3 Y 1
person1 day4 N 2
person1 day5 N 2
person1 day6 Y 3
person1 day7 Y 3
person1 day8 Y 3
I think there is way to get above result using analytic functions such as ROW_NUMBER, LEAD etc.
339
ROWNUM is the sequential number, allocated to each returned row during query execution. ROW_NUMBER assigns a number to each row according to its ordering within a group of rows. ROW_NUMBER is a function that returns numeric value.
You cannot use the NAME column to sort! UPDATE (SELECT name, order_id FROM test1 ORDER BY order_id) SET order_id = ROWNUM; Does not produce the expecting result because the order by is not used.
You can use ROWNUM to limit the number of rows returned by a query, as in this example: SELECT * FROM employees WHERE ROWNUM < 10; If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed.
HAVING refers to properties of groups created by GROUP BY , not to individual rows. You can't use ROWNUM in HAVING any more than you can use BYTES , or any other expression that may have different values for rows within a single group.
You can combine the analytic functions SUM (used as a running total) and LAG:
SQL> WITH data AS (
2 SELECT 'person1' person, 'day1' day, 'Y' flag FROM dual
3 UNION ALL SELECT 'person1' person, 'day2' day, 'Y' flag FROM dual
4 UNION ALL SELECT 'person1' person, 'day3' day, 'Y' flag FROM dual
5 UNION ALL SELECT 'person1' person, 'day4' day, 'N' flag FROM dual
6 UNION ALL SELECT 'person1' person, 'day5' day, 'N' flag FROM dual
7 UNION ALL SELECT 'person1' person, 'day6' day, 'Y' flag FROM dual
8 UNION ALL SELECT 'person1' person, 'day7' day, 'Y' flag FROM dual
9 UNION ALL SELECT 'person1' person, 'day8' day, 'Y' flag FROM dual
10 )
11 SELECT person, DAY, flag, SUM(gap) over (PARTITION BY person
12 ORDER BY DAY) grp
13 FROM (SELECT person, DAY, flag,
14 CASE WHEN flag = lag(flag) over (PARTITION BY person
15 ORDER BY DAY)
16 THEN 0
17 ELSE 1
18 END gap
19 FROM DATA);
PERSON DAY FLAG GRP
------- ---- ---- ----------
person1 day1 Y 1
person1 day2 Y 1
person1 day3 Y 1
person1 day4 N 2
person1 day5 N 2
person1 day6 Y 3
person1 day7 Y 3
person1 day8 Y 3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
