---
title: "SQL Server pick random (or first) value with aggregation"  
description: "SQL Server pick random (or first) value with aggregation"  
author: "Anonymous User"  
published: 2013-05-07  
updated: 2013-05-07  
canonical: https://www.mindstick.com/forum/827/sql-server-pick-random-or-first-value-with-aggregation  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 3 minutes  

---

# SQL Server pick random (or first) value with aggregation

Hi Everyone!\
How can I get [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) to return the first [value](https://www.mindstick.com/articles/23219/an-optimized-description-adds-value-to-experience-and-in-turn-effectively-guest-posting-packages) (any one, I don't care, it just needs to be [fast](https://www.mindstick.com/articles/12289/build-your-store-the-fast-way-with-magento-2-frontend-builder)) it comes across when aggregating?\
For example, let's say I have:\
ID [Group](https://yourviews.mindstick.com/view/81323/adani-green-energy-group-bags-world-s-biggest-solar-bid)1 A2 A3 A4 B5 Band I need to get any one of the ID's for each group. I can do this as follows:\
[Select](https://www.mindstick.com/forum/160534/orderby-then-select-vs-select-then-orderby-performance) max(id),group from [Table](https://www.mindstick.com/articles/43918/how-to-design-table-using-bootstrap) group by groupwhich returns\
ID Group3 A5 BThat does the job, but it seems stupid to me to ask SQL Server to calculate the highest ID when all it really needs to do is to pick the first ID it comes across.\
Thanks\
PS - the [fields](https://www.mindstick.com/forum/159126/sql-query-for-all-tables-and-fields-in-an-oracle-db) are indexed, so maybe it doesn't really make a [difference](https://www.mindstick.com/articles/157114/good-news-or-bad-news-and-the-difference-is)?\

## Replies

### Reply by AVADHESH PATEL

Hi Ezra!\
There is an undocumented aggregate called ANY which is not valid syntax but is possible to get to appear in your execution plans. This does not provide any performance advantage however.\
Assuming the following table and index structure\
CREATE TABLE T(id int identity primary key,[group] char(1) )\
CREATE NONCLUSTERED INDEX ix ON T([group])\
INSERT INTO TSELECT TOP 1000000 CHAR( 65 + ROW_NUMBER() OVER (ORDER BY @@SPID) % 3)FROM sys.all_objects o1, sys.all_objects o2, sys.all_objects o3I have also populated with sample data such that there are many rows per group.\
Your original query\
SELECT MAX(id), [group]FROM TGROUP BY [group] Gives Table 'T'. Scan count 1, logical reads 1367 and the plan\
|--Stream Aggregate(GROUP BY:([[T].[group]) DEFINE:([Expr1003]=MAX([[T].[id]))) |--Index Scan(OBJECT:([[T].[ix]), ORDERED FORWARD)Rewritten to get the ANY aggregate...\
;WITH cte AS(SELECT *, ROW_NUMBER() OVER (PARTITION BY [group] ORDER BY [group] ) AS RNFROM T)SELECT id, [group]FROM cte WHERE RN=1Gives Table 'T'. Scan count 1, logical reads 1367 and the plan\
|--Stream Aggregate(GROUP BY:([[T].[group]) DEFINE:([[T].[id]=ANY([[T].[id]))) |--Index Scan(OBJECT:([[T].[ix]), ORDERED FORWARD)Even though potentially [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) [Server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over) could stop processing the group as soon as the first value is found and skip to the next one it doesn't. It still processes all rows and the logical reads are the same.\
For this particular example with many rows in the group a more efficient version would be a recursive CTE.\
WITH RecursiveCTEAS ( SELECT TOP 1 id, [group] FROM T ORDER BY [group] UNION ALL SELECT R.id, R.[group] FROM ( SELECT T.*, rn = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM T JOIN RecursiveCTE R ON R.[group] < T.[group] ) R WHERE R.rn = 1 )SELECT *FROM RecursiveCTEOPTION (MAXRECURSION 0);Which gives\
Table 'Worktable'. Scan count 2, logical reads 19Table 'T'. Scan count 4, logical reads 12The logical reads are much less as it retrieves the first row per group then seeks into the next group rather than reading a load of records that don't contribute to the final result.


---

Original Source: https://www.mindstick.com/forum/827/sql-server-pick-random-or-first-value-with-aggregation

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.