April 11, 2012 at 12:53 am
I want to return one (top) row for each set of events, a set of events are defined by having the same displayserial, objectid and eventnumber within a 5 minute time period of the most recent timestamp in that set of events.
So in the example below row 5 would be considered a set of events with row 4 because they are both eventnumber 206, objectserial HV62, displayserial 6897913 and the timestamp of row 5 is within 5 minutes of row 4.
What I need is a select statement that would only return one row in a set of events, the one row i need to return would be the row with the earliest timestamp, so in my example row 4 would be the returned row for that set of events, rows 1,2 and 3 would also be returned as they are their own set of events, in the example below rows 6 and 7 would also form a set of events, row 6 would be the row I would need returning, row 7 would not be returned.
IDEVENTNUMBERDISPLAYSERIALDISPLAYIDOBJECTSERIALOBJECTRSSILEVELOBJECTSTATUSOBJECTIDTIMESTAMP
45587201-E08B-4856-A95D-6E5EE50D9C112066897913HV 64468858194766WAP 8 12:12:15 AM
2AC220DA-A109-400E-B17D-C7ADA260064B2066897913HV 64468778073954MV 20 12:16:31 AM
F76A2784-EDCF-4F28-BCE4-576801C86BDB1036897913HV 644689805251118HV 63 12:20:14 AM
3E115411-68F9-45AC-AF60-0C6F4FA223132066897913HV 644688613041122HV 62 12:21:02 AM
F1EBB667-BB0C-476C-BF7B-DD579F6C9A172066897913HV 644688613057106HV 62 12:21:51 AM
3D03604E-007C-41CE-9E2D-A7886A312B572046897913HV 644688613051106HV 62 12:21:53 AM
F930E2F2-62C0-4D99-B5D4-526DE3A980B62046897913HV 644688613052106HV 62 12:21:54 AM
63C4B012-E5CE-4177-B6FE-79631086AAF21046897913HV 644688613050106HV 62 12:22:07 AM
April 11, 2012 at 1:28 am
could you please follow the second link in my signature block on how to post code so that we can help you out.
if you can provide create table statements, refactor the data in your original code to insert into the table and show your expected results, we can help you quicker.
April 11, 2012 at 9:53 am
Look up ROW_NUMBER() in bol.
Basic syntax is:
select * from
(
select ROW_NUMBER() over (partion by SomeColumn order by SomeOtherColumn) as RowNum
from SomeTable
) x
where x.RowNum = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 3:28 pm
http://msdn.microsoft.com/en-us/library/ms186734.aspx in case google is broken. also if you post DDL and sample data you will get tested code. we here like solving problems. it passes the day away.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 11, 2012 at 5:37 pm
Sorry guys, I read the forum etiquette, quite right too....
CREATE TABLE [dbo].[ExampleLog](
[ID] [uniqueidentifier] NOT NULL,
[EVENTNUMBER] [int] NULL,
[DISPLAYSERIAL] [int] NULL,
[DISPLAYID] [varchar](50) NULL,
[OBJECTSERIAL] [int] NULL,
[OBJECTRSSILEVEL] [int] NULL,
[OBJECTID] [varchar](50) NULL,
[TIMESTAMP] [datetime] NULL)
INSERT INTO [dbo].[ExampleLog]
VALUES
(NewID(), 206, 6897913, 'HV 644', 6885819, 66, 'WAP 8', '2012-01-01 12:12:15'),
(NewID(), 206, 6897913, 'HV 644', 6885819, 66, 'WAP 8', '2012-01-01 12:12:15'),
(NewID(), 206, 6897913, 'HV 644', 6877807, 54, 'MV 20', '2012-01-01 12:16:31'),
(NewID(), 103, 6897913, 'HV 644', 6898052, 118, 'HV 63', '2012-01-01 12:20:14'),
(NewID(), 206, 6897913, 'HV 644', 6886130, 122, 'HV 62', '2012-01-01 12:21:36'),
(NewID(), 206, 6897913, 'HV 644', 6886130, 106, 'HV 62', '2012-01-01 12:21:51'),
(NewID(), 204, 6897913, 'HV 644', 6886130, 106, 'HV 62', '2012-01-01 12:21:53'),
(NewID(), 204, 6897913, 'HV 644', 6886130, 106, 'HV 62', '2012-01-01 12:21:54'),
(NewID(), 104, 6897913, 'HV 644', 6886130, 106, 'HV 62', '2012-01-01 12:22:07')
April 11, 2012 at 5:56 pm
I think this is what you are looking for:
with cte as (select ROW_NUMBER () OVER (PARTITION BY EVENTNUMBER ORDER BY TIMESTAMP ASC) as ROW, * FROM ExampleLog)
SELECT * from cte where ROW = 1
we row number based on event number (you can add DISPLAYSERIAL and DISPLAYID if you need it in your production tables by using PARTITION BY col1, col2, col3 ...) then order the row number by the date time (if you need the last time stamp its desc the earliest use asc as i have) and then in our outer query we select row number 1.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 11, 2012 at 7:17 pm
OK capn, this is great stuff,
I'm also going to use a sub query to return rows where the timestamp is within + 5 minutes after each of the rows returned above.
Thanks mate, you're a legend
April 11, 2012 at 11:33 pm
I’m still not over the line with this one, what I’ve found is my sub query doesn’t appear to be doing what I want, I get the exact same results with or without the sub query, let me know if you have any ideas as to where I've gone wrong....
[font="Courier New"]WITH cte AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY OBJECTID, DISPLAYID ORDER BY [TIMESTAMP]) AS tmp_rank
FROM ExampleLog)
SELECT * FROM cte
WHERE tmp_rank = 1
AND cte.ID NOT IN (
SELECT ID
FROM cte AS sq
WHERE sq.EVENTNUMBER = cte.EVENTNUMBER
AND sq.OBJECTSERIAL = cte.OBJECTSERIAL
AND sq.DISPLAYSERIAL = cte.DISPLAYSERIAL
AND sq.[TIMESTAMP] > cte.[TIMESTAMP]
AND sq.[TIMESTAMP] <= DATEADD(MINUTE, 5, cte.[TIMESTAMP])
)
ORDER BY cte.EVENTNUMBER, cte.OBJECTSERIAL, cte.DISPLAYSERIAL, cte.[TIMESTAMP][/font]
April 12, 2012 at 7:04 am
run the query that forms the common table expression and take a look at the row number. once you see that it will be clear why even with your sub query you get the same results.
SELECT *,
ROW_NUMBER() OVER(PARTITION BY OBJECTID, DISPLAYID ORDER BY [TIMESTAMP]) AS tmp_rank
FROM ExampleLog
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 12, 2012 at 7:07 am
if those results are not what you need can you post your expected results from the sample data.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 12, 2012 at 7:24 am
I modified your cte a little and I think these might be the results you're looking for
;WITH cte AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY EventNumber, OBJECTID, ObjectSerial ORDER BY [TIMESTAMP]) AS tmp_rank
FROM ExampleLog)
SELECT CurRow.ID, CurRow.EventNumber, CurRow.DisplaySerial, CurRow.DisplayID,
CurRow.ObjectSerial, CurRow.ObjectRSSILevel, CurRow.ObjectID,
CurRow.TimeStamp, NextRow.TimeStamp
FROM cte CurRow
inner join cte NextRow
on CurRow.EventNumber = NextRow.EventNumber
and CurRow.ObjectID = NextRow.ObjectID
and CurRow.ObjectSerial = NextRow.ObjectSerial
and NextRow.[TIMESTAMP] <= DATEADD(MINUTE, 5, CurRow.[TIMESTAMP])
and CurRow.tmp_Rank = NextRow.tmp_Rank - 1
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 12, 2012 at 7:32 am
Mike01 (4/12/2012)
I modified your cte a little and I think these might be the results you're looking for
;WITH cte AS (SELECT *,
ROW_NUMBER() OVER(PARTITION BY EventNumber, OBJECTID, ObjectSerial ORDER BY [TIMESTAMP]) AS tmp_rank
FROM ExampleLog)
SELECT CurRow.ID, CurRow.EventNumber, CurRow.DisplaySerial, CurRow.DisplayID,
CurRow.ObjectSerial, CurRow.ObjectRSSILevel, CurRow.ObjectID,
CurRow.TimeStamp, NextRow.TimeStamp
FROM cte CurRow
inner join cte NextRow
on CurRow.EventNumber = NextRow.EventNumber
and CurRow.ObjectID = NextRow.ObjectID
and CurRow.ObjectSerial = NextRow.ObjectSerial
and NextRow.[TIMESTAMP] <= DATEADD(MINUTE, 5, CurRow.[TIMESTAMP])
and CurRow.tmp_Rank = NextRow.tmp_Rank - 1
looking at his original data was a pain so i only half read them. that looks close to what he wants but it would still be nice for the OP to post nice consumable sample results so we stop guessing.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 12, 2012 at 7:50 am
I agree with you
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 12, 2012 at 7:43 pm
I need to find a way of limiting the partitions from the cte to rows that have occurred within a 5 minute of the latest timestamp in the partition, so...
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY OBJECTID, DISPLAYID ORDER BY TIMESTAMP) AS tmp_rank
FROM ExampleLog)
the above query partitions all rows where the objectid and displayid are the same and orders them by timestamp, now I want to limit the partition to rows that contain the same objected and displayed AND ALSO have a timestamp within 5 minutes of the latest timestamp in that partition, if no more rows meet the 5 minute criteria a new partition is created, below is a mockup of what I ideally want returned
[font="Courier New"]Row#IDEVENTNUMBERDISPLAYSERIALDISPLAYIDOBJECTSERIALOBJECTRSSILEVELOBJECTIDTIMESTAMPtmp_rank
197D4E0A1-31A0-4BDC-85D3-6CE1E8C1B0982066897913HV 644688581966WAP 82012-02-19 18:45:00.0001
2CFAF16E1-2FBA-49C6-A501-7CEB028C43D72066897913HV 644688581966WAP 82012-02-19 18:46:00.0002
33EC05E10-DE13-4799-9CDA-C67CA621FF842066897913HV 644687780754WAP 82012-02-19 18:59:00.0001
4DF4DC88D-1ABE-46A9-BDC1-7B65CBE094412066897913HV 6446898052118HV 622012-02-19 19:18:00.0001
5E6FA1702-D537-484B-94F7-5229E735EA4C2066897913HV 6446886130122HV 622012-02-19 19:22:00.0002
6B73575FB-0002-4E51-94D7-25BDDA36491A2066897913HV 6446886130106HV 622012-02-19 19:24:00.0003
79B39F192-D917-4A07-8A59-F24C75A464172046897913HV 6446886130106HV 622012-02-19 19:38:00.0001
8601B8C91-3D26-445D-B311-BD9A1A7526322046897913HV 6446886130106HV 622012-02-19 19:39:00.0002
9A12FB721-F73A-4078-9168-85F9068126C92066897913HV 6446886130106HV 622012-02-19 19:45:00.0001[/font]
TL/DR: I want to limit the partition to rows with a timestamp within 5 minutes of the latest timestamp in the partition, if no rows have a timestamp within 5 minutes of the latest timestamp, start a new partition
April 13, 2012 at 6:14 am
I'm trying to understand what you are looking for, but you seem to be all over the place. Your original post said that you were looking for rows where the eventnumber, objectserial, displayserial were the same and the timestamp was within 5 minutes of the previous row. Now you're looking for objected and displayed AND ALSO have a timestamp within 5. Are these 2 separate issues you are asking about? It also looks like the data you provided doesn't match you're expected results
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply