1

I made automated test GUI's using labview that test our 8 channel devices. Each time a test is completed, 8 rows are automatically added to a SQL Server table, and all of these 8 rows have the same Serial number recorded. Every time a test is done, 8 rows are added regardless of whether this serial number has already been tested. What I want to do is get the most recent data (always 8 Lines) for all the unique Serial Numbers.

So far I have only been able to do this for single line entry tests using the following code: 

 SELECT TestIndex 
 FROM dbo.TROSA_101_InitialTestingLD_AsicLpTest_CH
 WHERE TestIndex IN (SELECT MAX(TestIndex) FROM dbo.TROSA_101_InitialTestingLD_AsicLpTest_CH GROUP BY SerialNumber)

Essentially I just want to remove the duplicate sets of 8 lines of data(each corresponding to one test) or in other words get the most recent 8 lines for each unique SerialNumber value.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Mattias Payne
  • 23
  • 3

1 Answers1

0

I don't completely follow your business logic, but the requirement in the last sentence of your question is easy to come by using ROW_NUMBER:

SELECT *
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY SerialNumber ORDER BY TestIndex DESC) rn
    FROM dbo.TROSA_101_InitialTestingLD_AsicLpTest_CH
) t
WHERE rn <= 8;

The above query would return the 8 most recent records for each serial number, with "recent" referring to the greatest TestIndex value.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks! This worked perfectly! And yeah, the logic may not make the most sense lol. We are in an R&D environment where I am responsible for many different tasks, so almost nothing is optimized! Though I am curious what you mean if you have a different suggestion for how things should be done. :) – Mattias Payne Jul 04 '19 at 05:09