Proper Setup of Rank Tables

Topic

New Reply

I’m working on setting up ranking table that should rank 3 months apps. 6 months apps and 12 month apps.

The ranks should be laid out from 1-total number of records.
For some reason it’s not do this.

Could someone please take a look at the code below or the attached database and help me understand why my formulas are not working out.

I really appreciate the time and help, I’ve been working on this all weekend

Corey

[sql]SELECT Wholesale_Group_1_export_tbl.[OMNI#], Wholesale_Group_1_export_tbl.[3MonthTotalRecords#],
(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecords#] < B.[3MonthTotalRecords#]) AS 3MonthRank, Wholesale_Group_1_export_tbl.[6MonthTotalRecords#],
(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[6MonthTotalRecords#] < B.[6MonthTotalRecords#]) AS 6MonthRank,
Wholesale_Group_1_export_tbl.[12MonthTotalRecords#],
(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[12MonthTotalRecords#] < B.[12MonthTotalRecords#]) AS 12MonthRank
FROM Wholesale_Group_1_export_tbl;[/sql]

Reply | Quote

Replies

Corey,

sorry, I’m having trouble getting my head around the result you are aiming for. I can offer a quick suggestion, though.

Several of your fields are actually totals subqueries, e.g. 3MonthRank: (Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecords#] < B.[3MonthTotalRecords#]). You do not have a primary key on the table, but I assume that OMNI# holds unique values.

Why not have this as a separate query grouped by OMNI#, and pull in the result? You could do the same for the other subqueries. It is easier to debug when steps are broken down.

This might be a first step to a solution.

Regards,

Jules

Reply | Quote

Several of your fields are actually totals subqueries, e.g. 3MonthRank: (Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecords#] < B.[3MonthTotalRecords#]). You do not have a primary key on the table, but I assume that OMNI# holds unique values.

Why not have this as a separate query grouped by OMNI#, and pull in the result? You could do the same for the other subqueries. It is easier to debug when steps are broken down.

To do as you suggest would require (potentially) hundreds or thousands of subqueries, as the sql above is counting the number of records where 3MonthTotalRecords# is less than 3MonthTotalRecords# in the current record. So the subquery is different for each row.

Reply | Quote

I stand corrected!

Jules

Reply | Quote