| Author |
 Topic  |
|
|
dimepop
Starting Member
30 Posts |
 Posted - 08/10/2012 : 06:55:27
|
Hi, i have the following query which works fine:
SELECT TOP (10) UPPER(displayName) AS Devices, SUBSTRING(message, LEN('to') + 28, CHARINDEX(' failures ', message) - LEN('to') - 19) AS Pings
FROM eventlogevents
WHERE (type = 2)
But when i try to Add Distinct so it only shows distinct devices, the query Runs for a long time and usually times out.
Is there a way to select Distinct Devices in better way?
Thanks
|
|
|
NickC
Yak Posting Veteran
United Kingdom
68 Posts |
Posted - 08/10/2012 : 07:00:53
|
Temp table? then try it ?
Select Distinct * from #temp table~?
|
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/10/2012 : 07:05:17
|
Distinct would select multiple rows for a given displayname if the second column is different. In other words, distinctness test applies to every column in the select list.
You can try the following - not sure if that will be any more efficient though:SELECT
UPPER(displayName) AS Devices,
MAX(
SUBSTRING(
MESSAGE,
LEN('to') + 28,
CHARINDEX(' failures ', MESSAGE) - LEN('to') - 19
)) AS Pings
FROM eventlogevents
WHERE (TYPE = 2)
GROUP BY UPPER(displayName); |
|
|
|
dimepop
Starting Member
30 Posts |
Posted - 08/10/2012 : 07:35:49
|
Hi, thanks for the help, unfortunately your query errors with the message:
"Msg 537, Level 16, State 6, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function"
Regards
quote:
Originally posted by sunitabeck
Distinct would select multiple rows for a given displayname if the second column is different. In other words, distinctness test applies to every column in the select list.
You can try the following - not sure if that will be any more efficient though:SELECT
UPPER(displayName) AS Devices,
MAX(
SUBSTRING(
MESSAGE,
LEN('to') + 28,
CHARINDEX(' failures ', MESSAGE) - LEN('to') - 19
)) AS Pings
FROM eventlogevents
WHERE (TYPE = 2)
GROUP BY UPPER(displayName);
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 08/10/2012 : 07:58:37
|
quote:
Originally posted by dimepop
Hi, thanks for the help, unfortunately your query errors with the message:
"Msg 537, Level 16, State 6, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function"
Regards
quote:
Originally posted by sunitabeck
Distinct would select multiple rows for a given displayname if the second column is different. In other words, distinctness test applies to every column in the select list.
You can try the following - not sure if that will be any more efficient though:SELECT
UPPER(displayName) AS Devices,
MAX(
SUBSTRING(
MESSAGE,
LEN('to') + 28,
CHARINDEX(' failures ', MESSAGE) - LEN('to') - 19
)) AS Pings
FROM eventlogevents
WHERE (TYPE = 2)
GROUP BY UPPER(displayName);
This error is happening because of the CHARINDEX function in the second column. My guess is that there are rows where MESSAGE does not have the word ' failures ' and so one of the parameters to the SUBSTRING function ends up being negative:SUBSTRING(message, LEN('to') + 28, CHARINDEX(' failures ', message) - LEN('to') - 19)I did not quite follow what you are trying to do there - can you explain with some examples? |
Edited by - sunitabeck on 08/10/2012 07:59:24 |
|
|
|
dimepop
Starting Member
30 Posts |
Posted - 08/10/2012 : 09:03:07
|
Hi, i think you are right and some rows don't have the word 'failures'
I have added more conditions to make sure it contains the word failure and seems fine.
I have to do more tests but your query seems to work anyway.
Thanksquote:
Originally posted by sunitabeck
quote:
Originally posted by dimepop
Hi, thanks for the help, unfortunately your query errors with the message:
"Msg 537, Level 16, State 6, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function"
Regards
quote:
Originally posted by sunitabeck
Distinct would select multiple rows for a given displayname if the second column is different. In other words, distinctness test applies to every column in the select list.
You can try the following - not sure if that will be any more efficient though:SELECT
UPPER(displayName) AS Devices,
MAX(
SUBSTRING(
MESSAGE,
LEN('to') + 28,
CHARINDEX(' failures ', MESSAGE) - LEN('to') - 19
)) AS Pings
FROM eventlogevents
WHERE (TYPE = 2)
GROUP BY UPPER(displayName);
This error is happening because of the CHARINDEX function in the second column. My guess is that there are rows where MESSAGE does not have the word ' failures ' and so one of the parameters to the SUBSTRING function ends up being negative:SUBSTRING(message, LEN('to') + 28, CHARINDEX(' failures ', message) - LEN('to') - 19)I did not quite follow what you are trying to do there - can you explain with some examples?
|
|
|
| |
Topic |
|
|
|
Help with Distinct on 2 columns
