# Get top row for muliple registers

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 )
)

• As we all want to make our code more efficient or improve it in one way or another, try to write a title that summarizes what your code does, not what you want to get out of a review. Please see How to get the best value out of Code Review - Asking Questions for guidance on writing good question titles. – BCdotWEB Feb 29 '16 at 15:21
• – BCdotWEB Feb 29 '16 at 15:23

### 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

--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.

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;

• I managed to get it to work using ROW_NUMBER(). I want to see how it compares to using APPLY, but I can't figure out why the APPLY query returns @top number of records from mrh2 for every record in mrh1. – Steven Liekens Mar 1 '16 at 8:59
• I may have got my syntax incorrect, I don't use APPLY very often (more typically use ROW_NUMBER()), I'll check it again soon and try to fix it – Phrancis Mar 1 '16 at 12:16