How to Select the First Row of Each Group in SQL?

Select the first row of each group in SQL

In database management, it is often necessary to retrieve specific data sets for analysis or reporting. This involves selecting the first row of each record group in the table.

Suppose you have two tables:

• SM_Employee (employeeid, roleid, storeid)
• SM_SalesRepWorkflow (workflowid, salesrepid, quantityassigned, quantityleft, month, year)

Question:

Your task is to select the first row of details for each sales representative (SalesRepId) in the SM_SalesRepWorkflow table for the current month and year.

Solution:

For this you can use the ROW_NUMBER() function in 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 subquery
WHERE rownumber = 1;

Explanation:

• The outer query assigns each record a row number, partitions by SalesRepId column, and sorts by workflowid in ascending order.
• The WHERE clause filters the results to include only the first record of each SalesRepId.

This query effectively identifies the first row of records for each sales representative in the specified month and year. It provides a concise output as shown in the problem statement:

• WF_101 : EMP_101 : 100 : 90 : May : 2013
• WF_101 : EMP_102 : 100 : 100 : May : 2013
• WF_101 : EMP_103 : 100 : 80 : May : 2013

By applying the ROW_NUMBER() function, you can efficiently retrieve the first row of data for each grouping in the table for further analysis or reporting based on the required parameters.

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