Detect Rare Changes of Status in Very Large Recordset 2

[NWildblood]

I have a very large table that has five rows per Identifier (five rows represents five days' activity). There are many identifiers in the identifier column. Next to the identifier column is a "status" column. The (vast) majority of the time the status remains the same against each identifier, however I need some SQL to help report on any change in status against the occasional identifiers where this status change has happened. This would probably take me seconds in Access, but the recordset is huge, so am using SQL Developer which I am brand new to - any help very gratefully received.



No hay nada como un buitre cabrón como un ojo de cristal.

 

[LKBrwnDBA]

Try something like this to get list of status changes:

SELECT Id
  FROM (
    SELECT Id, RANK () OVER ( PARTITION BY Id ORDER BY Status) Rk 
      FROM Large_Tab)
 WHERE Rk > 1;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[NWildblood]

Thanks LKBrwnDBA, however

I'm getting a syntax error '(missing operator) in query expression 'RANK () OVER (PARTITION BY IADM_UNIQUE_ID ORDER BY REJECTION_REASON) RANK'

The code I am using is as follows (please excuse any rookie errors here in interpretation - have tried adapting your suggestion with a few alterations, but always getting syntax error.)


The Unique IDs are IADM_UNIQUE_ID
The Status is REJECTION_REASON
The large Access Table is Rejection_File_Master

SELECT IADM_UNIQUE_ID
FROM (
SELECT IADM_UNIQUE_ID, RANK () OVER (PARTITION BY IADM_UNIQUE_ID ORDER BY REJECTION_REASON) RANK
FROM Rejection_File_Master)
WHERE RANK > 1;

No hay nada como un buitre cabrón como un ojo de cristal.

 

[NWildblood]

UPDATE

On reading other forums, I have discovered that the SQL Query is entirely processed in MS Access, however MS Access does not support the
RANK() OVER function(A commenter could find neither keyword in the list of reserved keywords for Access' SQL dialect)

As a result I can't use the Rank() Over (Partition By....) statement.

If there are any alternative options I'd be glad to hear them !

Thanks again

OOS



No hay nada como un buitre cabrón como un ojo de cristal.

 

[LKBrwnDBA]

You had an error in oracle sql because either:

1) You are using obsolete Oracle version 9 or
2) You cannot use (RANK) keyword as alias:

. . . . R BY REJECTION_REASON) RANK

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: I don't do Windoze.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[taupirho]

what about selecting the min and max rejection reason per ID. if the min <> max there's been a change. Not sure how quick it would be though

In order to understand recursion, you must first understand recursion.

 

[LKBrwnDBA]

Good work-around taupirho!


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[NWildblood]

thanks taupirho will check that out tomorrow - appreciated.

No hay nada como un buitre cabrón como un ojo de cristal.