×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Detect Rare Changes of Status in Very Large Recordset
2

Detect Rare Changes of Status in Very Large Recordset

Detect Rare Changes of Status in Very Large Recordset

(OP)
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.

RE: Detect Rare Changes of Status in Very Large Recordset




Try something like this to get list of status changes:

CODE

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

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

RE: Detect Rare Changes of Status in Very Large Recordset

(OP)
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.

RE: Detect Rare Changes of Status in Very Large Recordset

(OP)
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.

RE: Detect Rare Changes of Status in Very Large Recordset


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:

CODE

. . . . R BY REJECTION_REASON) RANK 
banghead


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

RE: Detect Rare Changes of Status in Very Large Recordset

PS: I don't do Windoze.

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

RE: Detect Rare Changes of Status in Very Large Recordset

2
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.

RE: Detect Rare Changes of Status in Very Large Recordset


Good work-around taupirho!
orientalbow

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

RE: Detect Rare Changes of Status in Very Large Recordset

(OP)
thanks taupirho will check that out tomorrow - appreciated.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close