It ranks them up. With values likes: 10 25 25 40, the respective ranks
would be 1 3 3 4
You can use the primary key to break any tie (on the Sales value), like:
-----------------------------
SELECT X.[origin st] , X.[origin city], X.[destn st] , X.[destn city],
COUNT(*) AS rank
FROM tableName AS X
INNER JOIN tableName AS Y
ON X.[origin st] = Y.[origin st]
AND X.[origin city] = Y.[origin city]
AND X.[destn st] = Y.[destn st]
AND X.[destn city] = Y.[destn city]
AND (
( X.sales > Y.sales )
OR
( X.Sales=Y.Sales AND X.pk >= Y.pk )
)
GROUP BY X.[origin st], X.[origin city], X.[destn st] , X.[destn city]
---------------------------------
or use any other tie-breaker test.
Note that this query uses a join rather than a sub-query, but is not
editable graphically.
Vanderghast, Access MVP
Steve S said:
How does this code resolve ties?
Marshall Barton said:
subs said:
subs wrote:
i have a table like this
name dept sales rank
ram purchase 230 1
bill purchase 120 2
mol purchase 40 3
steve purchase 24 4
ram logistics 89 1
ram logistics 23 2
bill logistics 12 3
mol logistics 4 4
steve logistics 2 5
I already have the first three columns in one of my tables. I want
to
create a query which can give me the fourth column the rank column.
Rankings change once the dept changes as you could see above. How to
create a query to create a new column like rank
SELECT [name], dept, sales,
(SELECT Count(*) FROM table As X
WHERE X.dept = table.dept
And X.sales >=
table.sales) As rank
FROM table
- Show quoted text -
Thank you so much it worked. But what if i need the rankings change
after four different columns change in values. like here above
rankings change once the field dept changes. Say suppose i need the
rankings change once four different columns change - like
origin st origin city destn st destn
city carrier price Rank
A B C
D X 40 1
A B C
D Y 80 2
A B C
D Z 90 3
A1 B1 C1
D1 X 89 1
A1 B1 C1
D1 Y 99 2
Just change the
WHERE X.dept = table.dept
And X.sales >= table.sales
to
WHERE X.[origin st] = table.[origin st]
And X.[origin city] = table.[origin city]
And X.[destn st] = table.[destn st]
And X.[destn city] = table.[destn city]
And X.sales >= table.sales
