Select First Row of Every Group in sql

Question

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.

Order By SalesRepId,WorkflowId and for every SalesRepId i want the first row of that SalesRepId. — Vijay, May 13 '13 at 19:42

score 45 · Accepted Answer · answered May 13 '13 at 19:55

45

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

answered May 13 '13 at 19:55

Rachcha

8,486
8
48
70

Just looking for this thank you. – Vijay May 13 '13 at 20:12

score 0 · Answer 2 · edited Jan 17 '14 at 02:57

0

I'm having a little trouble understanding the question so if I'm off I'll edit it after, but it looks like you want to do something like SELECT * FROM SM_SalesRepWorkflow WHERE Workflowid = latest where latest is the id of the workflow. Perhaps you can determine the latest outside of the query and bring that in instead of doing it from the query?

edited Jan 17 '14 at 02:57

László Papp

51,870
39
111
135

answered May 13 '13 at 19:36

BWStearns

2,567
2
19
33

4

This is more of a comment than an answer. Please avoid doing this lest your posts will get flagged. – Rachcha Jan 06 '14 at 04:38
I gave an answer I thought addressed the question but also asked OP to reclarify. It seemed wasteful to post a comment expressing confusion and then an overly certain answer. – BWStearns Jan 16 '14 at 17:04

Select First Row of Every Group in sql

2 Answers2

Linked

Related