Select Distinct In Where Clause? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Select Distinct In Where Clause?

Author

Topic

Juz
Starting Member

2 Posts

Posted - 2014-07-14 : 02:35:27

Hello All,

Just wondering if it's possible to select distinct results from the where clause?

Thanks
Juz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-07-14 : 03:16:57

What do you mean exactly ? Example ?

KH
[spoiler]Time is always against us[/spoiler]

Juz
Starting Member

2 Posts

Posted - 2014-07-14 : 04:09:56

quote:
Originally posted by khtan

What do you mean exactly ? Example ?

KH
[spoiler]Time is always against us[/spoiler]

I am using a remote management system called Labtech and it has monitors that allow you to monitor the data in the SQL data base. The full query from the monitor is:

Create Temporary Table Tcomp (INDEX (Computerid)) SELECT SubGroupwchildren.computerid,SubGroupwchildren.GroupID FROM SubGroupwchildren WHERE  FIND_IN_SET(SubGroupwchildren.groupid,'1256') AND SubGroupwchildren.ComputerID NOT IN (Select ComputerID from AgentIgnore Where AgentID=649789);Select DISTINCT 'C',computers.computerid,computers.Name as ComputerName,Convert(CONCAT(clients.name,' ',locations.name) Using utf8) As Location FROM (( Computers LEFT JOIN Locations ON Locations.LocationID=Computers.Locationid) LEFT JOIN Clients ON Clients.ClientID=Computers.clientid) JOIN AgentComputerData on Computers.ComputerID=AgentComputerData.ComputerID WHERE Computers.ComputerID NOT IN (Select ComputerID From eventlogs WHERE eventlogs.`LogName` = 'Veeam Backup' AND  ((eventlogs.`LogName` = "Veeam Backup" AND eventlogs.`Message`LIKE "%Session%%has been completed.%") AND (eventlogs.`LogName` = "Veeam Backup" AND eventlogs.`Message` NOT LIKE "%failed%") and timegen > DATE_SUB(CURRENT_DATE(), INTERVAL 6 HOUR)) ) AND Computers.ComputerID IN (Select ComputerID From Tcomp);  Drop TEMPORARY Table Tcomp;

I only have the option to edit the WHERE clause. So I was wondering from the WHERE clause can I filter the data with the DISTINCT clause?

Thanks
Juz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-07-14 : 04:23:25

no. The DISTINCT is acted upon the SELECT column list

you will need to change the SELECT part of the statement.

Another way to get distinct result is to use GROUP BY

SELECT col1, col2, col3
FROM   yourtable
GROUP BY col1, col2, col3

It will give the same distinct result

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources