February 21, 2011 at 6:18 am
Hi,
I am having a table which contains iislogs in standard format.From that table i have to find top 50 records from average of top 90% records.
Thanks in Adavance.
Abhas.
February 21, 2011 at 6:25 am
top 50 records from average of top 90% records
Top by what? Can you post table creation script, some sample data and the expected results?
-- Gianluca Sartori
February 21, 2011 at 7:50 am
Whatever acts as the aggregate to get you the top 90% is also the thing that you can order by to get the first 50 using TOP 50 with the ORDER BY statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2011 at 9:50 pm
hi Sartori,
table structure is same as presented in below link.
and i want to display servernames as mentioned condition above post.
thank u
Abhas
February 21, 2011 at 11:06 pm
Please check below sql , might help you
select top 50 * from
(select top (90) percent * from table_name order by column_name ) t
order by column_name
February 22, 2011 at 1:57 am
abhas (2/21/2011)
hi Sartori,table structure is same as presented in below link.
and i want to display servernames as mentioned condition above post.
thank u
Abhas
I guess you have to extract top 50 rows by date/time for each server, based on a sample of 90% (by date/time?) of the whole table.
Take a look at this example:
--Check if temp table already exists, then drop it
IF OBJECT_ID('tempdb..#iislog') IS NOT NULL DROP TABLE #iisLog
--Create temp table
CREATE TABLE #iisLog (
[Client IP address] char(15),
[User name] nvarchar(128),
[Date] datetime,
[Time] datetime,
[Service and instance] nvarchar(20),
[Server name] nvarchar(20),
[Server IP] char(15),
[Time taken] int,
[Client bytes sent] int,
[Server bytes sent] int,
[Service status code] char(3),
[Windows status code] int,
[Request type] varchar(10),
[Target of operation] nvarchar(128),
[Parameters] varchar(128)
)
--Insert some random data into temp table
--Don't worry if you don't understand everything here, it's just to generate some data
INSERT INTO #iisLog
SELECT TOP(100000) -- Limit rows
[Client IP address] = '192.168.1.' + CAST(ABS(CHECKSUM(NEWID())) % 254 AS varchar(3)),
[User name] = '-',
[Date] = DATEADD(day, ABS(CHECKSUM(NEWID())) % 31, DATEADD(month, ABS(CHECKSUM(NEWID())) % 12, '20110101') ),
[Time] = DATEADD(second, ABS(CHECKSUM(NEWID())) % 86400, 0),
[Service and instance] = 'W3SVC2',
[Server name] = 'SERVER' + RIGHT('0' + CAST(serverNumber AS varchar(2)), 2),
[Server IP] = '10.0.1.' + CAST(serverNumber AS varchar(2)),
[Time taken] = ABS(CHECKSUM(NEWID())) % 5000,
[Client bytes sent] = ABS(CHECKSUM(NEWID())) % 5000,
[Server bytes sent] = ABS(CHECKSUM(NEWID())) % 5000,
[Service status code] = CASE WHEN V1.number % 17 = 0 THEN '500' ELSE '200' END,
[Windows status code] = CASE WHEN V1.number % 13 = 0 THEN 1 ELSE 0 END,
[Request type] = CASE WHEN V1.number % 8 = 0 THEN 'POST' ELSE 'GET' END,
[Target of operation] = '/fileName' + RIGHT('0' + CAST(ABS(CHECKSUM(NEWID())) % 99 AS varchar(2)), 2) + '.aspx',
[Parameters] = '-'
FROM master.dbo.spt_values V1
CROSS JOIN master.dbo.spt_values V2
CROSS APPLY (
SELECT ABS(CHECKSUM(NEWID())) % 99 AS serverNumber
) AS CA
WHERE V1.type = 'P' AND V2.type = 'P'
--Select TOP 50 rows for each server
SELECT *
FROM (
--Select 90 % of rows from temp table by Date + Time of request
SELECT TOP(90) PERCENT *,
--Add a windowed function to calculate the row number
RN = ROW_NUMBER() OVER (PARTITION BY [Server name] ORDER BY DATEADD(second, DATEDIFF(second, 0, [Time]), [date]))
FROM #iisLog
ORDER BY DATEADD(second, DATEDIFF(second, 0, [Time]), [date])
) AS NinetyPercent
WHERE RN <= 50
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply