RSS

Select the first row of each group

25 Jul

This example demonstrates how to select the first records grouping by Salesman and Store.

Sales:

ID Store Salesman Amount
1 A Lin 120.00
2 A Lin 80.50
3 A Fields 200.00
4 B Lin 160.50
5 B Lin 70.00
6 A Fields 130.00
7 B Fields 180.00
8 A Fields 110.50

The result should be like this:

Result:

ID Store Salesman Amount
3 A Fields 200.00
7 B Fields 180.00
1 A Lin 120.00
4 B Lin 160.50

The first method uses Row_Number() to count the row number of each group and returns only those records where row number is 1:

SELECT ID, Store, Salesman, Amount
  FROM (SELECT ID,
               Store,
               Salesman,
               Amount,
               ROW_NUMBER() OVER(PARTITION BY Salesman, Store ORDER BY Salesman) AS RowNo
          FROM Sales)
 WHERE RowNo = 1

The 2nd method only works when the ID column is unique. It gets the first ID value of each group and then returns those records where ID value equals the first ID value:

SELECT ID, Store, Salesman, Amount
  FROM (SELECT First_Value(ID) OVER(PARTITION BY Salesman, Store ORDER BY Salesman) FirstID,
               ID,
               Store,
               Salesman,
               Amount
          FROM Sales)
 where ID = FirstID;

Reference:How to select the first row of each group?

Advertisements
 
Leave a comment

Posted by on July 25, 2012 in Database, Oracle, SQL

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: