Determine sequence in log

In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
any way to extract a pattern of rows? Specifically, I am trying to select al
l
records that have an audit_log_id of '13' which would be immediately followe
d
by a record with an audit_log_id of '16', which in turn would be immediately
followed by a record with an audit_log_id of '2', and display the results in
these groups of three records?
Thanks for any help.
SteveCan you post some ddl, sample data and expected result?
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Steve B" wrote:

> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Only if there is some field, IN THE TABLE, that allows you to "determine" ro
w
sequence. I mean if there exists a ccolun, with unique values, which when
sorted, will sequence the rows in the order in which "immediately following
"
has the meaning you want it to have. If that's so, let's say that column i
s
named <LogDate>.
You have to join the table to itself, where, for each record in the first
instance of the table is "Joined" to it's Immediate Follower, by "Joining"
based on the value of LogDate being equal to the Minimum value of all the
records with LogDate > Than this records LogDate...
Select Prev.*, Next.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:

> In an AUDIT_LOG table, with approximately 1.6 million rows, would there be
> any way to extract a pattern of rows? Specifically, I am trying to select
all
> records that have an audit_log_id of '13' which would be immediately follo
wed
> by a record with an audit_log_id of '16', which in turn would be immediate
ly
> followed by a record with an audit_log_id of '2', and display the results
in
> these groups of three records?
> Thanks for any help.
> Steve|||Thanks Alejandro.
Below is an example. I would like to return the sets of rows that are 13,
16, 2. THere are three examples in this table.
AL_EVENT_ID AL_DATETIME
2 9/24/04 9:16 AM
13 9/24/04 9:51 AM
2 9/24/04 10:21 AM
13 9/24/04 10:39 AM
13 9/24/04 1:17 PM
2 9/24/04 1:23 PM
13 9/24/04 2:05 PM
2 9/24/04 2:40 PM
2 9/24/04 2:43 PM
13 9/24/04 2:56 PM
2 9/29/04 8:13 AM
2 9/29/04 4:11 PM
2 9/30/04 8:32 AM
16 10/1/04 11:38 AM
13 10/1/04 12:28 PM
16 10/1/04 12:29 PM
2 10/1/04 12:29 PM
2 10/1/04 2:30 PM
2 10/1/04 2:46 PM
2 10/1/04 3:04 PM
13 10/1/04 4:40 PM
2 10/1/04 4:44 PM
13 10/1/04 4:58 PM
2 10/4/04 9:42 AM
13 10/4/04 3:23 PM
16 10/4/04 3:23 PM
2 10/4/04 3:23 PM
2 10/5/04 4:56 PM
2 10/6/04 9:35 AM
13 10/6/04 11:52 AM
13 10/6/04 12:47 PM
16 10/6/04 12:49 PM
2 10/6/04 12:49 PM
2 10/6/04 1:32 PM
13 10/6/04 4:06 PM
2 10/7/04 8:51 AM
2 10/7/04 11:39 AM
13 10/7/04 11:49 AM
"Alejandro Mesa" wrote:
> Can you post some ddl, sample data and expected result?
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
>
> "Steve B" wrote:
>|||Thanks very much. I'm sure this is what I need. Not quite clear though on th
e
Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't match
.
I have added some example data to Alejandro's post.
Steve
"CBretana" wrote:
> Only if there is some field, IN THE TABLE, that allows you to "determine"
row
> sequence. I mean if there exists a ccolun, with unique values, which when
> sorted, will sequence the rows in the order in which "immediately followi
ng"
> has the meaning you want it to have. If that's so, let's say that column
is
> named <LogDate>.
> You have to join the table to itself, where, for each record in the first
> instance of the table is "Joined" to it's Immediate Follower, by "Joining"
> based on the value of LogDate being equal to the Minimum value of all the
> records with LogDate > Than this records LogDate...
> Select Prev.*, Next.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > First.LogDate)
> Join AUDIT_LOG Last On
> Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
> Where LogDate > Mid.LogDate)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
>
>
> "Steve B" wrote:
>|||Sorry they were table aliases in my first incantation, and neglected to
change them... They should be First, Mid, and Last
The SQL should be
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > First.LogDate)
Join AUDIT_LOG Last On
Last .LogDate = (Select Min(LogDate) From AUDIT_LOG
Where LogDate > Mid.LogDate)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Using your actual column name,
Select First.*, Mid.*, Last.*
From AUDIT_LOG First
Join AUDIT_LOG Mid On
Mid.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > First.AL_DATETIME)
Join AUDIT_LOG Last On
Last.AL_DATETIME =
(Select Min(AL_DATETIME) From AUDIT_LOG
Where AL_DATETIME > Mid.AL_DATETIME)
Where First.audit_log_id = 13
And Mid.audit_log_id = 16
And Last.audit_log_id = 2
"Steve B" wrote:
> Thanks very much. I'm sure this is what I need. Not quite clear though on
the
> Prev.*, Next.*, and Last.*. It returns Msg 107 - column prefix doesn't mat
ch.
> I have added some example data to Alejandro's post.
> Steve
> "CBretana" wrote:
>|||Thank once again for your help. It works like a charm
Steve
"CBretana" wrote:
> Using your actual column name,
> Select First.*, Mid.*, Last.*
> From AUDIT_LOG First
> Join AUDIT_LOG Mid On
> Mid.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > First.AL_DATETIME)
> Join AUDIT_LOG Last On
> Last.AL_DATETIME =
> (Select Min(AL_DATETIME) From AUDIT_LOG
> Where AL_DATETIME > Mid.AL_DATETIME)
> Where First.audit_log_id = 13
> And Mid.audit_log_id = 16
> And Last.audit_log_id = 2
> "Steve B" wrote:
>|||Yr very welcome!
"Steve B" wrote:
> Thank once again for your help. It works like a charm
> Steve
> "CBretana" wrote:
>