CODEWITHSUNDEEP
sqldatetime
JGG
JGG

Reputation: 71

SQL query for filtering duplicate rows of a column by the minimum DateTime of those corresponding rows

I have a SQL database table, "Helium_Test_Data", that has multiple entries based on the KeyID column (the KeyID represents a single tested part ). I need to query the entries and only show one entry per KeyID (part) based on the earliest creation date-time (format example is 2018-12-29 08:22:11.123). This is because the same part was tested several times but the first reading is the one I need to use. Here is the query currently tried:

SELECT mt.*
FROM Helium_Test_Data mt
INNER JOIN
(
    SELECT 
          KeyID, 
          MIN(DateTime) AS DateTime
      FROM Helium_Test_Data
      WHERE PSNo='11166565'
      GROUP BY KeyID
) t ON mt.KeyID = t.KeyID AND mt.DateTime = t.DateTime
  WHERE PSNo='11167197' 
  AND (mt.DateTime > '2018-12-29 07:00') 
  AND (mt.DateTime < '2018-12-29 18:00') AND OK=1 
  ORDER BY KeyId,DateTime

It returns only the rows that have no duplicate KeyID present in the table whereas I need one row per every single KeyID (duplicate or not). And for the duplicate ones, I need the earliest date.

Thanks in advance for the help.

Upvotes: 0

Views: 1083

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32021

use row_number() window function which support most dbms

select * from 
(
 select *,row_number() over(partition by KeyID order by DateTime) rn
from Helium_Test_Data
) t where t.rn=1

or you could use corelated subquery

select t1.* from Helium_Test_Data t1
where t1.DateTime= (select min(DateTime)
                   from Helium_Test_Data t2
                    where t2.KeyID=t1.KeyID
                   )

Upvotes: 1

Related Questions