Get top row for muliple registers

Question

I came across a piece of code that generates SELECT queries from a template.

The template:

SELECT TOP 1 * 
FROM   MRH_0000004 WITH (nolock) 
WHERE  MachineRegisterId = ${id}
ORDER  BY UtcTime

When the code has to retrieve multiple records, it generates one large query that gets the union of all individual queries based on the template above.

Example:

SELECT * 
FROM   (SELECT TOP 1 * 
        FROM   mrh_0000004 WITH (nolock) 
        WHERE  machineregisterid = 55 
        ORDER  BY utctime) q 
UNION ALL 
SELECT * 
FROM   (SELECT TOP 1 * 
        FROM   mrh_0000004 WITH (nolock) 
        WHERE  machineregisterid = 56 
        ORDER  BY utctime) q 
UNION ALL 
SELECT * 
FROM   (SELECT TOP 1 * 
        FROM   mrh_0000004 WITH (nolock) 
        WHERE  machineregisterid = 57 
        ORDER  BY utctime) q 

This is a query that gets the TOP 1 result from each group, without using a GROUP BY clause. These chains can get quite long, resulting in large queries from the client to the server.

Can this query be refactored using GROUP BY?

Table schema:

CREATE TABLE [dbo].[mrh_0000004] 
  ( 
     [machineregisterid] [INT] NOT NULL, 
     [utctime]           [DATETIME] NOT NULL, 
     [value]             [FLOAT] NOT NULL, 
     CONSTRAINT [pk_MRH_0000004] PRIMARY KEY CLUSTERED ( [machineregisterid] ASC 
     , [utctime] ASC ) 
  ) 

Answer 1

WITH (nolock)

While there is nothing inherently wrong with using WITH (nolock) per-se, it's mostly meant to be used within transactions that have an isolation level that does cause locks, to mark that specific table as an exception to the locking. If your whole transaction is meant to not have any locks, just declare this at the top:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Use variables

This will potentially help you make the code more readable and flexible.

--Using a variable for readability and flexibility
--This would make it easier also to turn into a script 
--like a stored procedure in the future
DECLARE @top INT;
SET @top = 1;

Then you can do things like SELECT TOP (@top) * FROM ... and that will allow to easily adapt it if you want top 3, top 5, 10 etc. instead of just the top-most row.

---

Approach

As thoroughly explained and illustrated in Retrieving n rows per group on DBA.StackExchange site, there are two common ways of doing this which are simpler and much more efficient than UNION ALL.

Using ROW_NUMBER()

Per MSDN:

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

As applied to your query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @top INT;
SET @top = 1;

WITH grouped AS (
    SELECT 
      ROW_NUMBER() OVER
        --This is equivalent to GROUP BY + ORDER BY combined
        --where the first item in each group/partition will have
        --row number 1 and so on, based on the ORDER BY clause
        (PARTITION BY machineregisterid 
        ORDER BY utctime) AS row_num
      , *
    FROM mrh_0000004
)
SELECT * FROM grouped
--Using <= in case @top was greater than 1
WHERE row_num <= @top;

Using APPLY

According to TechNet:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

Again, as applied to your query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @top INT;
SET @top = 1;

SELECT 
    mrh1.machineregisterid,
    mrh2.utctime,
    mrh2.[value] --using brackets because VALUE is an ODBC reserved keyword
FROM mrh_0000004 AS mrh1
CROSS APPLY (
    SELECT TOP (@top) * 
    FROM mrh_0000004
    WHERE machineregisterid = mrh1.machineregisterid
    ORDER BY utctime
) AS mrh2
ORDER BY machineregisterid, utctime;