Johnny Johnny - 6 months ago 14
SQL Question

SQL Two columns of guids no duplicates

I am learning SQL by watching YouTube and I am trying to achieve the following.
I have a table with three Columns

Inspection Guid, SensorData Guid, DateTime


I want to get a table of Inspection Guid, SensorDataGuid, DateTime but with no duplicate Inspection Guids.

From (I have shortened the Guids for convenience)

6E814118 DA875776 2016-05-25 14:59:17.523
6E814118 BAEB255D 2016-05-25 14:59:17.523
4B7D4E90 BB717852 2016-05-25 14:58:46.260
06BB9435 2321EDD6 2016-05-25 14:58:16.870
06BB9435 D4168F8B 2016-05-25 14:58:16.870
8D9A8502 3EED26BE 2016-05-25 14:57:47.400
5C9AA885 77D342DD 2016-05-25 14:57:15.217
5C9AA885 43DEF213 2016-05-25 14:57:15.217


To

6E814118 DA875776 2016-05-25 14:59:17.523
4B7D4E90 BB717852 2016-05-25 14:58:46.260
06BB9435 2321EDD6 2016-05-25 14:58:16.870
8D9A8502 3EED26BE 2016-05-25 14:57:47.400
5C9AA885 77D342DD 2016-05-25 14:57:15.217


Sorted by date time.

Thank you very much for your help.

Answer

USE ROW_NUMBER() to choose the first GUID based on DATE and then ORDER BY to display.

SELECT *
FROM (
        SELECT [Inspection Guid], 
               [SensorData Guid], 
               [DateTime],
               ROW_NUMBER() OVER (PARTITION BY [Inspection Guid] 
                                  ORDER BY [DateTime] DESC) rn
        FROM YourTable
      ) T
WHERE rn = 1
ORDER BY [DateTime] DESC
Comments