Help with SQL to Rank Records

[Crusado]

Hi,

I have a query which brings back the following columns:

YEAR; PERIOD; STORE; PRODUCT; GP.

The table it is referring to is called 05_FULL_TABLE and I have changed the Alias for this table within my query to 05_FULL_TABLE1.

I'm trying to add a field which will rank the products by GP (Highest GP=1), within each group i.e. the rank starts from 1 again every time you hit a new year, period, or Store.

I've taken a suggested method from Microsft Support: http://support.microsoft.com/kb/208946
Seniority: (Select Count(*) from Employees Where [HireDate] < _
[Emp1].[HireDate]

and changed it to: RANK: (Select Count(*) from 05_FULL_TABLE Where ([SumOfGP] < [05_FULL_TABLE1].[SumOfGP] AND [STORE_FKEY] = [05_FULL_TABLE1].[STORE_FKEY] AND [YEAR] = [05_FULL_TABLE1].[YEAR] AND [PERIOD] = [05_FULL_TABLE1].[PERIOD]) +1)

It is adding a rank, but it is not starting the rank again at any of the changes in store etc (which are all sorted Ascending in the query result), so I guess my "WHERE" clause isn't right. Can anyone suggest where I've gone wrong?

Many Thanks

Andrew

 

[Dennisk]

what you require is to rank within a group

Here is one
SELECT mytable3.col1, mytable3.col2, IIf([col1]=[col1],(select count(*) from mytable3 a where
a.Col1 = mytable3.Col1 and a.uniq < mytable3.uniq)+1,"") AS col3
FROM mytable3;

and here is the table design

Col1 Number - Grouping
Col2 Date - Data
Uniq number - Unique Key

col1 col2 uniq
1111 06/11/06 1
1111 06/11/06 2
1111 06/11/06 3
1111 06/11/06 4
1111 06/11/06 5
1111 06/11/06 10
2222 06/11/06 120
2222 06/11/06 123
2222 06/11/06 1234
2222 06/11/06 1566
3333 06/11/06 1567
3333 06/11/06 1588

and this is the output from the query
col1 col2 col3
1111 06/11/06 1
1111 06/11/06 2
1111 06/11/06 3
1111 06/11/06 4
1111 06/11/06 5
1111 06/11/06 6
2222 06/11/06 1
2222 06/11/06 2
2222 06/11/06 3
2222 06/11/06 4
3333 06/11/06 1
3333 06/11/06 2

 

[Crusado]

thanks Dennisk, from looking at your example I've cobbled together something that works in the query:

RANK: IIf(([05_FULL_TABLE1].[YEAR]=[YEAR] And [05_FULL_TABLE1].[PERIOD]=[PERIOD] And [05_FULL_TABLE1].[STORE]=[STORE]),(SELECT COUNT([GP]) FROM 05_FULL_TABLE WHERE ([GP] > [05_FULL_TABLE1].[GP]) and ([05_FULL_TABLE1].[YEAR]=[YEAR] And [05_FULL_TABLE1].[PERIOD]=[PERIOD] And [05_FULL_TABLE1].[STORE]=[STORE])),"")+1