CODEWITHSUNDEEP
sql-serverdistinct
Glowie
Glowie

Reputation: 2309

SELECT Distinct based on few columns

I have an MS SQL Query 

SELECT DISTINCT Date_of_Record, Computer_Name, IP_Address, Agent_Version, Mac_Address
FROM [SEP_Versions].dbo.Computers
WHERE Computer_Name in (SELECT Computer_Name
                        FROM [SEP_Versions].dbo.Computers
                        GROUP BY Computer_Name
                        HAVING COUNT(DISTINCT Agent_Version) > 1)
AND [SEP_Versions].dbo.Computers.COMPUTER_NAME LIKE '%s001'
AND [SEP_Versions].dbo.Computers.IP_Address LIKE '%.100'
ORDER BY Computer_Name, Date_of_Record

That outputs

Date        Computer   IP               Version         Mac
2014-10-24  COMP001    10.10.10.10      12.1.3001.165   NULL
2014-10-25  COMP001    10.10.10.10      12.1.3001.165   NULL
2014-10-26  COMP001    10.10.10.10      12.1.3001.165   NULL
2014-10-27  COMP001    10.10.10.10      12.1.4013.4013  NULL
2014-10-28  COMP001    10.10.10.10      12.1.4013.4013  NULL
2014-10-29  COMP001    10.10.10.10      12.1.5773.2276  NULL

How to change the SELECT DISTINCT statement so it outputs records with distinct Version at the earliest Date, i.e

Date        Computer   IP               Version         Mac
2014-10-24  COMP001    10.10.10.10      12.1.3001.165   NULL
2014-10-27  COMP001    10.10.10.10      12.1.4013.4013  NULL
2014-10-29  COMP001    10.10.10.10      12.1.5773.2276  NULL

@Pradeep

Your solution outputs

Date        Computer   IP               Version         Mac
2014-10-17  COMP001    10.10.10.10      12.1.3001.165   NULL
2014-10-17  COMP002    10.10.10.11      12.1.4013.4013  NULL
2014-10-17  COMP003    10.10.10.12      12.1.5773.2276  NULL
2014-10-17  COMP004    10.10.10.14      11.0.5003.2276  NULL

@1010 and @AWinkle

If I run 

SELECT DISTINCT Computer_Name, IP_Address, Agent_Version
FROM dbo.Computers
WHERE Computer_Name in (SELECT Computer_Name
                        FROM dbo.Computers
                        GROUP BY Computer_Name
                        HAVING COUNT(DISTINCT Agent_Version) > 1)
AND dbo.Computers.COMPUTER_NAME LIKE '%s001'
AND dbo.Computers.IP_Address LIKE '%.100'
ORDER BY Computer_Name

Then I get 

Computer   IP               Version         Mac
COMP001    10.10.10.10      12.1.3001.165   NULL
COMP001    10.10.10.10      12.1.4013.4013  NULL
COMP001    10.10.10.10      12.1.5773.2276  NULL

How do I modify the SQL statement so it adds corresponding Date_of_Record column

Upvotes: 0

Views: 212

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93694

Simple answer is by using window function.

;WITH cte
     AS (SELECT DISTINCT Row_number()
                           OVER (
                             partition BY Computer_Name,Agent_Version
                             ORDER BY Date_of_Record) RN,
                         Date_of_Record,
                         Computer_Name,
                         IP_Address,
                         Agent_Version,
                         Mac_Address
         FROM   [SEP_Versions].dbo.Computers
         WHERE  Computer_Name IN (SELECT Computer_Name
                                  FROM   [SEP_Versions].dbo.Computers
                                  GROUP  BY Computer_Name
                                  HAVING Count(DISTINCT Agent_Version) > 1)
                AND [SEP_Versions].dbo.Computers.COMPUTER_NAME LIKE '%s001'
                AND [SEP_Versions].dbo.Computers.IP_Address LIKE '%.100')
SELECT Date_of_Record,
                     Computer_Name,
                     IP_Address,
                     Agent_Version,
                     Mac_Address
FROM   cte
WHERE  RN = 1
ORDER  BY Computer_Name,
          Date_of_Record

Upvotes: 0

AWinkle
AWinkle

Reputation: 673

In SQL Server, you cannot select distinct by only a select number of columns, you need to group them. Using the MIN function will provide distinct version (by computer/ip/mac) at the earliest date.

SELECT MIN(Date_of_Record) Earliest_Date_of_Record, Computer_Name, IP_Address, Agent_Version, Mac_Address
FROM [SEP_Versions].dbo.Computers
WHERE Computer_Name in (SELECT Computer_Name
                    FROM [SEP_Versions].dbo.Computers
                    GROUP BY Computer_Name
                    HAVING COUNT(DISTINCT Agent_Version) > 1)
AND [SEP_Versions].dbo.Computers.COMPUTER_NAME LIKE '%s001'
AND [SEP_Versions].dbo.Computers.IP_Address LIKE '%.100'
GROUP BY Computer_Name, IP_Address, Agent_Version, Mac_Address
ORDER BY Computer_Name, Date_of_Record

Upvotes: 3

1010
1010

Reputation: 1848

I think this is what you want

SELECT MIN(Date_of_Record), Computer_Name, IP_Address, Agent_Version, Mac_Address
FROM ...
WHERE ...
GROUP BY Computer_Name, IP_Address, Agent_Version, Mac_Address
ORDER BY ...

the group by clause will aggregate rows for each combination of Computer_Name, IP_Address, Agent_Version, Mac_Address. MIN(Date_of_Record) will calculate for each group the earliest date.

Upvotes: 1

Related Questions