ElizabethSchlag
Qrew Member
8 months ago

# Rank Using Query

I am trying to build a query that ranks the weights from high to low in a 1-X format. It is working, except when two records have the same weight it ranks them with the same number. Essentially, I want it to uniquely rank them. Is this possible in Quickbase?

Current Formula:

var text QUERY = "{355.GTE.'" & [Weight] & "'}AND{17.XEX.'Cancelled'}AND{17.XEX.'Complete'}AND{17.XEX.'Hold'}AND{9.XEX.'Research'}";

If([State]="Complete",null,
If([State]="Cancelled",null,
If([State]="Hold",null,
If([Type of Contract]="Research",null,

Size(GetRecords(\$QUERY))))))

------------------------------
Elizabeth Schlagel
------------------------------

### 9 Replies

• Interesting question and one that I don't recall being satisfactorily answered before.

Here is an example what worked for me to create an arbitrary tie breaker.  I created a new field which was fid 8 called [Value + Record ID# /10000]

The formula as you might guess was

[Value] + [Record ID#] / 10000

Then I ranked that field using this formula

var text QUERY = "{8.LTE.'" & [Value + Record ID# /10000] & "'}";

Size(GetRecords(\$QUERY))
.
.
.
Note that there were triplicate and duplicate ties for Values 23 and 100 which were broken by the Record ID# / 10000

------------------------------
mark.shnier@gmail.com
------------------------------

• I looked at this problem as an exercise in code for me.

I went and sorted Rank as Mark has from 'Lowest to Highest' and it is the same as above. BUT, one of the things I get frequently are the spaces between data. which I don't want most of the time?

------------------------------
Bob T.
------------------------------
• Your table report is sorting and grouping by rank. Just change it so it sort by rank. The spaces are because you have chosen to have groups by rank.

------------------------------