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