Get the first workflow for each sales representative from the SQL database

This article explores how to retrieve the first row of data for each sales representative from a database table, focusing on the presence of multiple workflows per representative, with the goal of obtaining the initial workflow for the month and the year.

To do this, we can use the ROW_NUMBER() function as follows:

SELECT *
FROM (
    SELECT 
        workflowid, 
        salesRepId, 
        quantityAssigned,
        quantityLeft, 
        month, 
        year,
        ROW_NUMBER() OVER (PARTITION BY salesRepId ORDER BY workflowid) AS rownumber
    FROM sm_salesRepWorkflow
) AS RankedWorkflows
WHERE rownumber = 1;

This query first assigns the line number to each workflow record by sales representative ID. Then, it selects only records with line number equal to 1.

Example usage

Consider the following data:

workflowidsalesRepIdquantityAssignedquantityLeftmonthyear &&]10090May2013WF_1012013EMP_10310080 &&]May2013WF_102May2013

WF_101	EMP_101
		E MP_102	100	100	May
	WF_101
			EMP_101	100	70

Execution of the query will retrieve the following results: yearEMP_10110090May &]2013WF_101MayEMP_103100May2013

	workflowid	salesRepId	quantityAssigned	quantityLeft	month
	WF_101
			EMP_102	100	100
2013		WF_101			80

These lines represent the first workflow for each representative in the specified month and year. Note that the sort of workflowid

determines the "first" workflow. If you need to determine the first workflow based on other columns, such as dates, you need to adjust the

ORDER BY clause.