Partioning a table of data for 2 distinct rows
-
Hi there
In the following example, I have a log table which captures events in a database
Now it records events relating to an sp called sUtilFinaliseDb and contains 2 tasks:1) 'sUtilFinaliseDB: Transfering File'
2) 'sUtilFinaliseDB: Backup Database'Now in the example i have a set of 6 of these but only want to count the 2 most recent
entries ie LogID = 11750 and 11757How can I parition this set of data , so i can always catch the most recent entries
for these 2 tasks?The code to create and populate the table is as follows:
-- DROP TEMP TABLE IF ALREADY EXISTS
If
OBJECT_ID(N'tempdb..#tempOutputTable', N'U') IS NOT NULL DROP TABLE #tempOutputTable
CREATE TABLE [#tempOutputTable](
[LogID] [int] NOT NULL,
[Category] [nvarchar](50) NULL,
[LogText] [nvarchar](max) NULL,
[LogDate] [datetime] NULL,
[FileImportID] [int] NULL,
[ErrorNumber] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [#tempOutputTable] ([LogID], [Category], [LogText], [LogDate], [FileImportID], [ErrorNumber]) VALUES (13733, N'FinaliseDB', N'sUtilFinaliseDB: Transfering Files To Archive Completed', CAST(N'2018-12-14T08:38:20.773' AS DateTime), NULL, NULL)
GO
INSERT [#tempOutputTable] ([LogID], [Category], [LogText], [LogDate], [FileImportID], [ErrorNumber]) VALUES (13739, N'FinaliseDB', N'sUtilFinaliseDB: Backup Database Completed', CAST(N'2018-12-14T08:38:53.317' AS DateTime), NULL, NULL)
GO
INSERT [#tempOutputTable] ([LogID], [Category], [LogText], [LogDate], [FileImportID], [ErrorNumber]) VALUES (11741, N'FinaliseDB', N'sUtilFinaliseDB: Transfering Files To Archive Completed', CAST(N'2018-12-13T13:07:58.183' AS DateTime), NULL, NULL)
GO
INSERT [#tempOutputTable] ([LogID], [Category], [LogText], [LogDate], [FileImportID], [ErrorNumber]) VALUES (11747, N'FinaliseDB', N'sUtilFinaliseDB: Backup Database Completed', CAST(N'2018-12-13T13:08:31.213' AS DateTime), NULL, NULL)
GO
INSERT [#tempOutputTable] ([LogID], [Category], [LogText], [LogDate], [FileImportID], [ErrorNumber]) VALUES (11750, N'FinaliseDB', N'sUtilFinaliseDB: Transfering Files To Archive Completed', CAST(N'2018-12-13T15:07:58.183' AS DateTime), NULL, NULL)
GO
INSERT [#tempOutputTable] ([LogID], [Category], [LogText], [LogDate], [FileImportID], [ErrorNumber]) VALUES (11757, N'FinaliseDB', N'sUtilFinaliseDB: Backup Database Completed', CAST(N'2018-12-13T15:12:31.213' AS DateTime), NULL, NULL)
GO
-
;with cteTransfer as
(
select TOP(1) *
from #tempOutputTable t
where t.LogText = 'sUtilFinaliseDB: Transfering Files To Archive Completed'
order by logdate desc
),
cteBackup AS
(
select TOP(1) *
from #tempOutputTable t
where t.LogText = 'sUtilFinaliseDB: Backup Database Completed'
order by logdate desc
)
select * from cteTransfer
union all
select * from cteBackup -
Hi Jonathan
Thanks for posting that.... The only thing is that i want to retrieve the 2 last entries ie LogID = 11750 and Log ID = 11757 and i dont think the code quite
does this? - Weegee2017 - Friday, December 14, 2018 4:28 AM
That's even simpler:
select TOP(2) *
from #tempOutputTable t
order by t.LogID desc -
Hi Jonathan
That wont retirieve the last 2 entries
- Weegee2017 - Friday, December 14, 2018 4:58 AM
You actually want the first two entries, not the last:
select TOP(2) *
from #tempOutputTable t
order by t.LogID ASC - Jonathan AC Roberts - Friday, December 14, 2018 5:06 AM
Ok that pulls out the middle entries (logid = 11741 and logid = 11747)
I want to retrieve dynamically the last 2 entries for these 2 tasks which are Logid (11750,11757)
and occuring at '2018-12-13 15:07:58.183' and '2018-12-13 15:12:31.213' respectively. - Weegee2017 - Friday, December 14, 2018 5:17 AM
;with cteTransfer as
(
select TOP(1) *
from #tempOutputTable t
where t.LogText = 'sUtilFinaliseDB: Transfering Files To Archive Completed'
order by t.LogID ASC
),
cteBackup AS
(
select TOP(1) *
from #tempOutputTable t
where t.LogText = 'sUtilFinaliseDB: Backup Database Completed'
order by t.LogID ASC
)
select * from cteTransfer
union all
select * from cteBackup - Jonathan AC Roberts - Friday, December 14, 2018 5:26 AM
Hi
That code brings back the LogID (11741 and 11747) which are the middle entries
I want to bring back the last 2 entries dynamically
- Weegee2017 - Friday, December 14, 2018 7:52 AMJonathan AC Roberts - Friday, December 14, 2018 5:26 AM
Hi
That code brings back the LogID (11741 and 11747) which are the middle entries
I want to bring back the last 2 entries dynamicallyWhat makes them the last 2? i.e. what column(s) would you order by to get the last 2?
- Weegee2017 - Friday, December 14, 2018 5:17 AM
First, tables represent sets, which are unordered. In order to apply an order, you have to have some field to order on. The only two applicable fields in your dataset are LogID and LogDate. The two records you want to choose are not the latest under any reasonable sort on either of those two fields.
Here is my solution.
SELECT TOP (1) WITH TIES *
FROM #tempOutputTable
ORDER BY ROW_NUMBER() OVER(PARTITION BY LogText ORDER BY LogDate DESC)Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply