How to Select the First Row of Each Group in SQL Using ROW_NUMBER()?

Get the first row of records for each group

In SQL, retrieving the first record of each unique grouping is a common task. Consider this situation, we have two tables, SM_Employee and SM_SalesRepWorkflow. By combining these two tables, we want to select the first row of information from SM_SalesRepWorkflow for each sales representative for the current month and year, sorted by SalesRepId.

To achieve this, we can take advantage of the functionality of the ROW_NUMBER() function. Here is a detailed explanation of the query:

<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
)
WHERE rownumber = 1;</code>

• The subquery calculates the row number for each sales representative based on salesRepId and sorts by workflowid in ascending order.
• The main query then filters the subquery to only include rows with rownumber 1, ensuring we retrieve the first row for each salesRepId.

By using the ROW_NUMBER() function, we can efficiently select the initial record for each sales representative while adhering to the specified conditions. This technique is useful in various scenarios where you need to group and select the first row of each group.

The above is the detailed content of How to Select the First Row of Each Group in SQL Using ROW_NUMBER()?. For more information, please follow other related articles on the PHP Chinese website!