Menu
I have two tables .
1-> SM_Employee
(1) employeeid (2) roleid (3) storeid
2-> SM_SalesRepWorkflow
(1) workflowid (2) Salesrepid foreign key to employeeid (3) QuantityAssigned (4) QuantityLeft (5) month (6) year
By these tables I need to select first row of every SalesRep Details from SM_SalesRepWorkflow order by SalesRepId for CurrentMonth and CurrentYear.
Example
Workflowid SalesRepId QuantityAssigned QuantityLeft Month Year
WF_101 : EMP_101 : 100 : 90 : May : 2013
WF_101 : EMP_102 : 100 : 100 : May : 2013
WF_101 : EMP_103 : 100 : 80 : May : 2013
WF_102 : EMP_101 : 100 : 70 : May : 2013
So result i want is
WF_101 : EMP_101 : 100 : 90 : May : 2013
WF_101 : EMP_102 : 100 : 100 : May : 2013
WF_101 : EMP_103 : 100 : 80 : May : 2013
So There can be many Workflow for a SalesRep. But i want the first one for every SalesRep for current month and year.
352
To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
The first way to find the first row of each group is by using a correlated subquery. In short, a correlated subquery is a type of subquery that is executed row by row. It uses the values from the outer query, that is, the values from the query it's nested into.
Adding the DISTINCT keyword to a SELECT query causes it to return only unique values for the specified column list so that duplicate rows are removed from the result set.
You can use the ROW_NUMBER() function like this:
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; Fiddle Demo
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
