Querying the Most Recent N Samples

An LIMS user submitted this request:

“How can I query the most recent 16 samples for a customer?”

A one-time query is simple. First, query either all the customer’s samples or recent samples using a date range criterion that results in more than 16 samples. Now use the [Select] button to open the Select Samples screen, right-click within the Login Order column and choose Sort Descending from the popup menu to place the samples in reverse chronological order. Use the [Clear All] button then enter 16 and click the [First N] button to select the first 16 samples.

In this case, the user needed a simpler solution with fewer steps since others would use the query frequently. For a repetitive query, an SQL expression is a good solution. You can add the following expression to the SQL Expressions screen to accomplish the same task:

SampleID IN (SELECT TOP 16 SampleID FROM Sample WHERE CustomerID=99 ORDER BY Format$(Sample.AddedDate,”yyyymmddhhnnss “) & Right$(Sample.SampleID,3) Desc)

Use the appropriate value in the “TOP N” clause in the expression. Replace the 99 with the specific customer’s internal CustomerID value, which you can find by reviewing the “SQL for last query” on the SQL tab of the query controls after querying any customer samples. While the example above uses only a customer criterion, you can expand the expression with multiple criteria as necessary.