How to Get the First Workflow for Each Sales Representative in SQL?

Get the first workflow for each sales rep from SQL database

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

For this we can use the ROW_NUMBER() function as shown below:

<code class="language-sql">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;</code>

This query first assigns a row number to each workflow record by sales representative ID. Subsequently, it selects only records with row number equal to 1.

Example usage

Consider the following data:

Executing the query will retrieve the following results:

These rows represent the first workflow for each representative in the specified month and year. Note that the ordering of workflowid determines the "first" workflow. If you need to determine the first workflow based on other columns (such as date), you need to adjust the ORDER BY clause.

The above is the detailed content of How to Get the First Workflow for Each Sales Representative in SQL?. For more information, please follow other related articles on the PHP Chinese website!