Re: Ranking - dealing with ties

Marshall

a) I didn't think until too late just to post the SQL - sorry. In any case,
I was able to use your post of Feb 17 to solve this problem. The SQL shown
below gives perfect results every time after numerous tests. FWIT, the '+1'
you asked about ensures the results look like 1,2T,2T,3 etc instead of
0,1T,1T,2...

qryDivScore has fields Score (Desc) and Div (Asc).
qryDistinctDivScore has field Score (Desc), with the Unique Value property
set.

I'm mildy surprised that this actually works because I had to refer to
qryDivScore using two different Aliases (S and T). I'm not sure if there is
a better way.

SELECT S.Div, S.Score, (SELECT Count(*) FROM [qryDistinctDivScore] AS D
WHERE [D].[Score] > [S].[Score])+1 AS SRank, IIf((SELECT Count(*) FROM
[qryDivScore] AS T WHERE T.Score = S.Score )>1,"T","") AS Tie, [SRank] &
[Tie] AS STRank
FROM [qryDivScore] AS S;

b) Your comments re Alias make a lot of sense. (removing ambiguity etc.)
--
Thanks
Sophie


"Marshall Barton" wrote:

Sophie wrote:
There are 3 queries: qryScores, qryDistinctScores, qryRankScores

qryRankScores has 3 fields:
1) Division (based on qryScore, Alias Scor1)
2) Score (also based on Scor1)
3) Rank: (Select Count (*) from qryDistinctScore _
Where [Score] > [Scor1].[Score])+1

This gives perfect rankings 1,2,2,3.. but I just need a way to append
those darn T's to the tied values.

If I may be allowed a follow up question - I really don't understand why an
Alias (Scor1) has to be used instead of the actual query name, qryScore.


When posting about a query, use Copy/Paste of the query's
SQL View. Anything else can be ambiguous and/or incomplete.
In this case, I can't figure out why the Where clause has
the +1. Maybe it has something to do with how qryScores was
put togeteher so you should also post this query.

I really need to figure out that +1 before I can be sure the
kind of calculated field I posted earlier will work (even
after correcting the missing right parenthesis).

Your question about alias is also confusing without seeing
the full SQL statement, but I think the reason is because
the subquery and the main query are both drawing from the
same data source. One of them must use an alias so you can
indicate which data source a field belongs to. Your Where
clause above lets Access guess (not a good thing to do)
which of the two data sources it shoud use for the
unqualified Score field.

Note that many experienced query writers often alias every
table/query in a From clause, if for no other reason, just
to make the names shorter and reduce the clutter in all the
field references.

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: mySQL Problem
    ... And when the query gets executed i get back the following error: ... Actually, problem is proper quoting, not the format or anything else. ... the SQL standard and won't work on any other RDBMS I'm familiar with. ... you admit that mssql uses something DIFFERENT and PARTICULAR to alias? ...
    (comp.lang.php)
  • RE: query tables
    ... For example here's your original SQL fixed up some: ... Now let's get rid of the A alias and plug in the actual table name: ... FROM [Weekly Demand] ... Open the query in design view. ...
    (microsoft.public.access.queries)
  • Re: Problem with SQL Task in MS SQL Server DTS package
    ... This is a long shot but can you remove the alias of your table as well. ... DTS is unable to offer you ... result set from a Sybase query into a MS SQL Server table. ...
    (microsoft.public.sqlserver.dts)
  • Re: TOP 3 of each group
    ... SQL from Access with the automatically added extra parenthesis: ... >>second query similar to the SQL you offered, ... > The Q is an alias for your query's name. ... Each SQL dialect ...
    (microsoft.public.access.reports)
  • Re: Please explain why this Select doesnt fail
    ... >> be qualified with table name or alias or else throw an error? ... Actually I would prefer that if the column in the subquery ... columns as soon as more than one table is used in a query) ... miracles with SQL also allows one to goof up big time. ...
    (microsoft.public.sqlserver.programming)