Posted By

sekihin on 12/25/13


Tagged

sql


Versions (?)

Select First Row of Every Group in sql


 / Published in: SQL
 

sss

  1. CREATE TABLE sm_salesRepWorkflow
  2. ( workflowid varchar2(50)
  3. , Salesrepid varchar2(50)
  4. , QuantityAssigned integer
  5. , QuantityLeft integer
  6. , month varchar2 (10)
  7. , year integer);
  8.  
  9.  
  10. INSERT INTO sm_salesRepWorkflow VALUES ('WF_101', 'EMP_101', 100, 90, 'May', 2013);
  11. INSERT INTO sm_salesRepWorkflow VALUES ('WF_101', 'EMP_102', 100, 100, 'May', 2013);
  12. INSERT INTO sm_salesRepWorkflow VALUES ('WF_101', 'EMP_103', 100, 80, 'May', 2013);
  13. INSERT INTO sm_salesRepWorkflow VALUES ('WF_102', 'EMP_101', 100, 70, 'May', 2013);
  14. INSERT INTO sm_salesRepWorkflow VALUES ('WF_103', 'EMP_102', 100, 110, 'Jun', 2013);
  15.  
  16. SELECT *
  17. FROM(SELECT workflowid, salesRepId, quantityAssigned,
  18. quantityLeft, month, year
  19. , ROW_NUMBER()
  20. OVER (PARTITION BY salesRepId
  21. ORDER BY workflowid) AS rownumber
  22. FROM sm_salesRepWorkflow)
  23. WHERE rownumber = 1;

Report this snippet  

You need to login to post a comment.