Oracle Partitioned Sequence

Oracle 12 - Reset a sequence Column with row_number() over Partition

Use a MERGE statement and correlate on the ROWID pseudo-column to efficiently self-join:

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         ROW_NUMBER() OVER (
           PARTITION BY product
           ORDER BY ROWNUM  -- or ORDER BY seq
         ) AS newSeq
  FROM   table_name
) src
ON ( dst.ROWID = src.rid )
WHEN MATCHED THEN
  UPDATE SET seq = src.newSeq;

(Note: You do NOT need an ORDER BY expression appended to the SELECT statement as it will have no practical effect as the order of the source query is irrelevant during the MERGE and could make the SELECT statement less efficient if the ordering is applied and the SQL engine does unnecessary work.)

Which, for the sample data:

CREATE TABLE table_name ( product, "GROUP", seq ) AS
SELECT 10,  5, 1 FROM DUAL UNION ALL
SELECT 10,  1, 5 FROM DUAL UNION ALL
SELECT 11,  2, 3 FROM DUAL UNION ALL
SELECT 11,  4, 4 FROM DUAL UNION ALL
SELECT 11, 24, 5 FROM DUAL

Then results in:

SELECT * FROM table_name

PRODUCT	GROUP	SEQ
10	5	1
10	1	2
11	2	1
11	4	2
11	24	3

SQL Partition By Date and Sequence

I think that what you're missing is that the date column includes a time component, and you need to remove that before using it in the PARTITION BY.

WITH
  sorted AS
(
  SELECT 
    row_number() OVER (
      PARTITION BY item, location, seqn, TRUNC(date)
          ORDER BY date
    )
      AS row_num,
    date,
    item,
    location,
    seqn
  FROM 
    table1
)
SELECT
  *
FROM
  sorted
WHERE
      row_num  = 1
  AND item     = 'A1234'
  AND location = 'L1'
ORDER BY 
  location,
  item,
  seqn,
  date

Oracle SQL: Further sort PARTITION BY groups based on first row in each partition

Luckily, you only need to add an analytic max() to the order by clause. You don't need to do anything else.

Suppose "current query" is your existing query, not ordered yet in any way (no order by clause). Add the following at the very end:

... existing query ...
order  by max(timetocomplete) over (partition by itemkey) desc,
          itemkey,
          timetocomplete desc
;

Note that you do not need to add the analytic function to the select clause. The SQL standard says you do; Oracle syntax says you don't. Oracle is taking care of the small additional steps for us, behind the scenes.

This computes the max time to complete for each key. It orders by that max first. In the case of ties (two or more different keys with the same max time to complete), it further orders by key first, and then within each key, by time to complete (descending).

Oracle SQL or PLSQL. Select rows by partitions which values have specific order

You can do it by only reading the table once using the Tabibitosan method to group sequential competitions together https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/#:%7E:text=The%20SQL%20of%20Gaps%20and%20Islands%20in%20Sequences,...%204%20Performance%20Comparison%20of%20Gaps%20Solutions.%20

Here you would have to use add_months because your competitions are months apart:

select sportsman_id, min(hold_date) , max(hold_date), comps_in_island
from (
 select  competition_id, sportsman_id, hold_date, island, count(*) over (partition by sportsman_id,island) comps_in_island
 from (
  select  competition_id, sportsman_id, hold_date , add_months(hold_date,-1*row_number() over(partition by sportsman_id order by hold_date)) island
  from    result
 )
)
where comps_in_island > 1
group by sportsman_id, island, comps_in_island;

DB fiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=1b707262722bc555ad851aee029b347a

-edit
I got confused by some of the data, it looks like it's not the date that's important but the competition_id. This makes it simpler, if you have a gapless competition_id sequence (so competition 65786162213 was 65.7 billion events after 4)

select sportsman_id, min(competition_id) , max(competition_id), comps_in_island
from (
 select  competition_id, sportsman_id, hold_date, island, count(*) over (partition by sportsman_id,island) comps_in_island
 from 
  select  competition_id, sportsman_id, hold_date , competition_id -row_number() over(partition by sportsman_id order by competition_id)) island
  from    result
 )
)
where comps_in_island > 1
group by sportsman_id, island, comps_in_island;

Or if you need to work out the competition numbers first you just need an additional subquery using dense_rank to rank the unique competition_ids accounting for ties :

select sportsman_id, min(competition_id) , max(competition_id), comps_in_island
from (
 select  competition_id, sportsman_id, hold_date, island, count(*) over (partition by sportsman_id,island) comps_in_island
 from (
  select  competition_id, sportsman_id, hold_date , comp_number -row_number() over(partition by sportsman_id order by comp_number) island
  from (  
   select  competition_id, sportsman_id, hold_date , dense_rank() over (partition by null order by competition_id) comp_number
   from    result
  )
 )
)
where comps_in_island > 1
group by sportsman_id, island, comps_in_island;

This does assume that every possible competion_id you care about has a row in result.

Oracle 12.2 - Replacement of NOPARTITION feature

The (no)partition option for sequences was never documented. And thus never supported.

There was a bug in 12.1 which exposed this via dbms_metadata. It no longer happens in 12.2

Undocumented features can (and as this proves) do change without warning. Using them is strictly at your own risk.

Oracle partition by group into date based sequence

First you should find GROUP_ID for each record to sort all similar COL1 to different GROUPS if they have a gap between. And then use this GROUP_ID in the OVER statement with COL1:

SQLFiddle demo

SELECT ROW_NUMBER() OVER (PARTITION BY Group_id,col1 ORDER BY col2) AS RNUM, a3.* 
FROM 
(
select a1.*,
      (select count(*) from t a2 where 
       a2.col1<>a1.col1 
       AND  
       a2.col2<a1.col2) as GROUP_ID
from t a1
) a3

order by col2

---

Related Topics

There Are a Method to Paging Using Ansi SQL Only

SQL Select Group by and String Concat

The Job Failed. the Job Was Invoked by User<User>. the Last Step to Run Was Step1

Is Not Null Test for a Record Does Not Return True When Variable Is Set

How to Call a Stored Proc from a Function

Check Constraint of String to Contain Only Digits. (Oracle SQL)

What Does the Pipe Operator Do in SQL

Foreign Key for Either-Or Column

SQL Select Where Column Begins with \

How to Connect a Localdb from Visual Studio to the Published Azure Web App

Simple Recursive Query in Oracle

Using Output Clause to Insert Value Not in Inserted

How to Find Fifth Highest Salary in a Single Query in SQL Server

Recursive Cte Stop Condition for Loops

Running Total Until Specific Condition Is True

Conversion Failed When Converting from a Character String to Uniqueidentifier Error in SQL Server

SQL - Returning All Rows Even If Count Is Zero for Item

SQL Server, Cascade Delete and Parent/Child Table

---

Leave a reply

Submit