Editing MSSQL Cookbook (section)

==Selecting the earliest records grouped by some other criteria==
'''Goal:''' You want to get the records with the earliest date for each group. 

'''Solution:''' Use <code>PARTITION BY</code> (SQL Server 2005 or greater).

<syntaxhighlight lang="sql">
WITH groupedCTE AS
(
	SELECT 
		id
		, groupName
		, createDate
		/* defining the group and creating a way to target by date value */
 		, ROW_NUMBER() OVER (PARTITION BY groupName ORDER BY createDate) AS row 
	FROM myTable
)
SELECT 
	id
	, groupName
	, createDate
FROM groupedCTE
/* filter down to the earliest record */
WHERE row = 1 
/* ORDER BY clause here isn't contributing to the solution; just demonstrates sorting the results */
ORDER BY groupName 
</syntaxhighlight>

'''Solution:''' Another approach using <code>PARTITION BY</code> 
The first solution seems more clear, and maybe it's also more efficient?

<syntaxhighlight lang="sql">
SELECT
	id
	, groupName
	, createDate
FROM 
(
	SELECT
		id
		, groupName
		, createDate
		MAX(createDate) OVER (PARTITION BY groupName) MaxCreateDate
	FROM myTable
) a1
/* filter down to the earliest record */
WHERE groupName = groupName
AND dtView = MaxCreateDate
/* ORDER BY clause here isn't contributing to the solution; just demonstrates sorting the results */
ORDER BY groupName
</syntaxhighlight>

'''Note''' that while <code>PARTITION BY</code> makes for very clear code, it is not super-fast for execution.
[[Category:Web Development]]
[[Category:MSSQL]]