SQL Server : How to get rownumber for each common set of values?

Question

Hi,

Say my table is like this...

`
Tenancy Number    Tenant Number    Tenant
1                   1               John
1                   2               Jane
1                   3               Pete
2                   56              Fred
2                   78              Jackie

How can I select the rownumber based on grouping by the tenany number? So far I can get the row number in, but I can't figure a way to "reset" that upon next tenancy number.

I would like to achieve the following results

MYROWNUM         Tenancy Number    Tenant Number        Tenant
    1                1                   1               John
    2                1                   2               Jane
    3                1                   3               Pete
    1                2                   56              Fred
    2                2                   78              Jackie

Sorry if I haven't explained this too well!

Thanks!

Answer 1

You can use the row_number() function for this.

Example:

 SELECT 
    SalesOrderID, OrderDate, 
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
 FROM 
    Sales.SalesOrderHeader

HTH

Answer 2

SELECT ROW_NUMBER() OVER (PARTITION BY [Tenancy Number]
                          ORDER BY [Tenant Number]
                         ) AS myrownum
       ,[Tenancy Number]
       ,[Tenant Number]
       ,Tenant
from <table>

Answer 3

Use the Row_Number() function:

DECLARE @t TABLE (TenancyNbr smallint, TenantNbr smallint, Tenant varchar(10))
INSERT INTO @t
SELECT 1,1,'Jack'
UNION ALL SELECT 1,2,'Janet'
UNION ALL SELECT 1,3, 'Chrissy'
UNION ALL SELECT 2,56, 'Mr. Roper'
UNION ALL SELECT 2,78, 'Mrs. Roper'


SELECT MyRowNum = ROW_NUMBER() OVER (PARTITION BY TenancyNbr ORDER BY TenantNbr),
    TenancyNbr, TenantNbr, Tenant
FROM @t