Most efficient way to take _n_ rows per group

Question

Most efficient way to take _n_ rows per group

Pål Kristian Halle 155

Hello ADX experts.

I have an ADX database with IoT sensor data. My use case is to retrieve a specified number of readings (rows) per MetricId after a certain date. The most natural way to do this seems to be using the partition by operator like this:

Metrics
| where MetricId in (1001, 1011, 1021)
| where Timestamp >= datetime(2024-06-01)
| partition hint.strategy=native by MetricId 
(
    top 2 by Timestamp asc
)

This gives me the result I want, but it's extremely slow:

950 ms when hint.strategy = native
700 ms when hint.strategy = shuffle

If I use the union operator, the response is very fast - only 30 ms:

union
(
	Metrics
    | where MetricId == 1001
    | where Timestamp >= datetime(2024-06-01)
    | top 2 by Timestamp asc
),
(
	Metrics
    | where MetricId == 1011
    | where Timestamp >= datetime(2024-06-01)
    | top 2 by Timestamp asc
),
(
	Metrics
    | where MetricId == 1021
    | where Timestamp >= datetime(2024-06-01)
    | top 2 by Timestamp asc
)

But this becomes cumbersome fast if I want to filter on 100 different metrics.

Are there any alternative, more performant ways to do this that I have overlooked?

This is my table schema:

.create table Metrics (
    MetricId: string,
    Timestamp: datetime,
    Value: real
)

.alter table Metrics policy partitioning ```
{
  "PartitionKeys": [
    {
      "ColumnName": "MetricId",
      "Kind": "Hash",
      "Properties": {
        "Function": "XxHash64",
        "MaxPartitionCount": 128,
        "PartitionAssignmentMode": "Uniform"
      }
    },
    {
      "ColumnName": "Timestamp",
      "Kind": "UniformRange",
      "Properties": {
        "Reference": "2000-01-01T00:00:00",
        "RangeSize": "1.00:00:00",
        "OverrideCreationTime": true
      }
    }
  ],
  "EffectiveDateTime":"2000-01-01T00:00:00"
}```

VRISHABHANATH PATIL 1,820 Reputation points Microsoft External Staff Moderator

2025-11-04T07:44:35.3266667+00:00

Hi Pål Kristian Halle,

Thank you for contacting Microsoft Q&A. Here are the detailed steps to help resolve the issue you reported.

Using partition by is correct for getting N rows per group, but the performance issue is expected because partition forces a distributed shuffle across partitions. For large datasets, this can be expensive.

Alternatives

Use summarize + top-hitters pattern Instead of partition, you can aggregate first and then apply top logic:

Metrics
| where MetricId in (1001, 1011, 1021)
| where Timestamp >= datetime(2024-06-01)
| summarize readings = make_list(pack('Timestamp', Timestamp, 'Value', Value), N) by MetricId

make_list() collects the top N rows per group when combined with top inside summarize.

This avoids the heavy shuffle that partition introduces.

Leverage take_any() or arg_min() / arg_max() for single-row scenarios If you only need the earliest or latest reading per MetricId:

Metrics
| where Timestamp >= datetime(2024-06-01)
| summarize arg_min(Timestamp, *) by MetricId

Extremely efficient because it uses aggregation rather than partitioning.

Dynamic union for small metric sets Your union () approach is fast because it avoids distributed operations, but it doesn’t scale well for 100+ metrics. For large sets, prefer to summarize or make_list().

Why partition is slow

partition creates multiple subqueries and redistributes data across nodes.

Even with hint.strategy=native, the cost grows with the number of partitions and rows.

KB articles.

https://learn.microsoft.com/azure/data-explorer/kusto/query/partitionoperator

https://learn.microsoft.com/azure/data-explorer/kusto/query/summarizeoperator

arg_min() (aggregation function) - Kusto | Microsoft Learn

Aggregation Functions - Kusto | Microsoft Learn

For N rows per group, prefer to summarize with make_list() or arg_min()/arg_max() for single-row cases. These patterns minimize shuffling and are much more performant than partition for large metric sets.
Pål Kristian Halle 155 Reputation points

2025-11-04T20:01:56.6933333+00:00
Thank you for your helpful suggestions and explanations!

You are right - arg_min and arg_max are super fast when only one row per metric is needed, even when querying 1000 different metrics.

Your suggestion to use make_list is clever, something I hadn't thought of. It's a little slower than desired (~500 ms), but there is also a bigger problem. It returns any N rows after the specified date.

If there are let's say 500k rows after the given date, N random rows of those are returned. In my use case it's important to get the N rows at or immediately following the filtered timestamp.

To achieve this, I need to adjust the query accordingly:

Metrics | where MetricId in (1001, 1011, 1021) | where Timestamp >= datetime(2024-06-01) | order by Timestamp asc | summarize readings = make_list(bag_pack('t', Timestamp, 'v', Value), N) by MetricId

Notice the added order by clause. This, however, slows the query down to about 3 seconds which is about the same as the row_number() approach.

Is it possible to define the order in a different way? I am surprised that this seems so difficult when the union approach is, after all, super fast.
Pål Kristian Halle 155 Reputation points

2025-11-05T07:09:52.8666667+00:00

Thanks again. I'll keep experimenting and see which approach I end up with. I'd like to accept your answer, but I can't since you posted it as a comment.
VRISHABHANATH PATIL 1,820 Reputation points Microsoft External Staff Moderator

2025-11-05T07:39:16.13+00:00

Hi Pål Kristian Halle,

Converted the solution into Answer section, you may proceed and "Accept' the answer.

Answer accepted by question author

1 additional answer

Your answer

Pål Kristian Halle 155 Reputation points

2025-11-04T20:01:56.6933333+00:00

Thank you for your helpful suggestions and explanations!

You are right - arg_min and arg_max are super fast when only one row per metric is needed, even when querying 1000 different metrics.

Your suggestion to use make_list is clever, something I hadn't thought of. It's a little slower than desired (~500 ms), but there is also a bigger problem. It returns any N rows after the specified date.

If there are let's say 500k rows after the given date, N random rows of those are returned. In my use case it's important to get the N rows at or immediately following the filtered timestamp.

To achieve this, I need to adjust the query accordingly:

Metrics | where MetricId in (1001, 1011, 1021) | where Timestamp >= datetime(2024-06-01) | order by Timestamp asc | summarize readings = make_list(bag_pack('t', Timestamp, 'v', Value), N) by MetricId

Notice the added order by clause. This, however, slows the query down to about 3 seconds which is about the same as the row_number() approach.

Is it possible to define the order in a different way? I am surprised that this seems so difficult when the union approach is, after all, super fast.
Pål Kristian Halle 155 Reputation points

2025-11-05T07:09:52.8666667+00:00

Thanks again. I'll keep experimenting and see which approach I end up with. I'd like to accept your answer, but I can't since you posted it as a comment.
VRISHABHANATH PATIL 1,820 Reputation points Microsoft External Staff Moderator

2025-11-05T07:39:16.13+00:00

Hi Pål Kristian Halle,

Converted the solution into Answer section, you may proceed and "Accept' the answer.

Answer 1

Hi Pål Kristian Halle,

Thank you for sharing your feedback. We have looked into the possible alternatives, and here are some steps that may help address the issue:

You are right about how make_list() works—it doesn’t keep the order unless you explicitly sort the data first. The catch is that adding a sort step before summarizing can slow things down, which explains the performance hit you noticed.

Here’s why that happens and what you can try instead:

Why does order by slow things down?

By default, summarize in Kusto works on unordered data.
When you add order by Timestamp asc, it forces a full sort across all rows before aggregation, which is costly for large datasets (think hundreds of thousands of rows).
The union method is faster because it skips the global sort and handles each metric separately.

Ways to improve performance:

Use arg_min / arg_max for first row only
- Great for single-row retrieval per metric, but not for N rows.
- Chunked approach with take after filtering
  - If you can tolerate approximate results:

KQL

Metrics

| where MetricId in (1001, 1011, 1021)

| where Timestamp >= datetime(2024-06-01)

| partition by MetricId

| take N

Limitation: Doesn’t guarantee strict ordering by timestamp.
mv-expand + top inside summarize
- Instead of global sort, sort within each group:

KQL

Metrics

| where MetricId in (1001, 1011, 1021)

| where Timestamp >= datetime(2024-06-01)

| summarize readings = make_list(pack('t', Timestamp, 'v', Value)) by MetricId

| mv-expand readings

| order by MetricId, readings.t asc

This reduces the cost of sorting across all rows.
Union-based approach for strict ordering
- If metrics list is small and static:

KQL

union (

Metrics | where MetricId == 1001 | where Timestamp >= datetime(2024-06-01) | top N by Timestamp asc,

Metrics | where MetricId == 1011 | where Timestamp >= datetime(2024-06-01) | top N by Timestamp asc,

Metrics | where MetricId == 1021 | where Timestamp >= datetime(2024-06-01) | top N by Timestamp asc

)

This avoids global sorts and is often fastest for small metric sets.

There’s no built-in way for make_list() to keep the order unless you sort the data first. If maintaining order is important, using a union or a partitioned approach is usually the most practical solution.

Answer 2

Vinodh247 40,031 MVP Volunteer Moderator

Hi ,

Thanks for reaching out to Microsoft Q&A.

Use row_number() with summarize and take instead of partition. It is typically faster and scales better for many MetricIds.

Example(sourced from web):

Metrics
| where MetricId in (1001, 1011, 1021)
| where Timestamp >= datetime(2024-06-01)
| extend rn = row_number(Timestamp asc, MetricId)
| where rn <= 2

If you have many MetricIds, prefilter data using ingestion_time() or limit by a recent time window. Also ensure the table is well partitioned on MetricId (as in your setup) and update statistics regularly to keep query planner efficient.

Please 'Upvote'(Thumbs-up) and 'Accept' as answer if the reply was helpful. This will be benefitting other community members who face the same issue.

Pål Kristian Halle 155 Reputation points

2025-11-04T07:22:05.85+00:00
Thanks for the prompt answer, but your example doesn't even have valid syntax.

row_number() doesn't accept column names, as can be seen here.

But if I go down the route of window functions, this performs even worse. The following query takes 2400 ms to complete:

Metrics | where MetricId in (1001, 1011, 1021) | where Timestamp >= datetime(2024-06-01) | order by MetricId asc, Timestamp asc | extend rn = row_number(1, MetricId != prev(MetricId)) | where rn <= 2

Other suggestions are more than welcome – including suggestions for adjusting my partitioning policy.

I also forgot to mention that each sensor (in this specific example) has approximately 5 million rows.

Share via

Most efficient way to take _n_ rows per group

1 additional answer

Your answer