Help with Distinct on 2 columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with Distinct on 2 columns

Author

Topic

dimepop
Starting Member

33 Posts

Posted - 2012-08-10 : 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

68 Posts

Posted - 2012-08-10 : 07:00:53

Temp table? then try it ?

Select Distinct * from #temp table~?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 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

33 Posts

Posted - 2012-08-10 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-10 : 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?

dimepop
Starting Member

33 Posts

Posted - 2012-08-10 : 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.
Thanks

quote:
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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources