That's solved it - thank you!!!!!!
:
Ok, since you want the top 10 WITH ties, we cannot use the primary key as
tie breaker (since we do not want to break the ties in the first place).
Try something like:
SELECT a.type, a.score
FROM myTable As a LEFT JOIN myTable As b
ON a.type = b.type AND a.score < b.score
GROUP BY a.type, a.score
HAVING COUNT(b.type) < 10
Note the inner join has been changed to an outer join, <= to < and COUNT(*)
as COUNT(unpreservedSideField)
Note that technically, the rank is, here, given by:
1+ COUNT(b.type)
Since you want rank <= 10, that makes: ( rank -1 ) < 10, or we could
have wrote
HAVING 1+COUNT(b.type) <= 10
or
HAVING COUNT(b.type) <= 9
but I just found prettier:
HAVING COUNT(b.type) <10
Simply artistic considerations, at that point.
Hoping it may help,
Vanderghast, Access MVP
I am almost there with this - I really appreciate your help!!!
I have:
SiteNumber, Type, Score
SiteNumber is unique. I group by Type and want the top 10 of each Type by
Score.
My last problem I have with the query you have written is that I have a
number of Scores that are in the top 10 but which are the same as for
other
sites (also in the top 10 obviously).
The query at the moment will not include any of the rows which have
duplicates in the Score field.
I either want the first 10 sites which appear when sorted from largest to
smallest Score. Or I want all of the sites that are captured from
returning
the 10 largest scores.
I don't care which of these I get - but I cannot allow any sites to be
disregarded.
If you can help it would be gretaly appreciated - I'm pulling my hair out
on
this one!!!
:
Change the > and >= for < and <= in the ON clause.
Vanderghast, Access MVP
This is nearly what I'm after - but how would you go about taking the
10
highest freight per employee?
Thanks for your help!!
:
Indeed, I messed up the query. Try the following on Northwind:
----------------------------------
SELECT a.EmployeeID, a.freight,
COUNT(*) AS rank, LAST(a.OrderID), LAST(a.OrderDate)
FROM Orders AS a INNER JOIN Orders AS b
ON a.employeeID = b.employeeID
AND ( a.freight > b.freight OR (a.freight=b.freight
AND a.OrderID >= b.OrderID))
GROUP BY a.EmployeeID, a.freight
HAVING COUNT(*) <=10
-----------------------------------
where I take the 10 lowest freight, per employee. If two freight are
equal,
the primary key is used to break the tie.
Vanderghast, Access MVP
I have a very similar problem, and due to the complexity of the
database
as
it currently stands, it is not practical to use the sub-query
option.
When
I
try applying the 'rank by group' solution you give, I end up with an
output
which returns (for each group) the 10 smallest pk numbers and then
sorts
each
of these groups of 10 entries by the 'sales' value.
Any ideas?
:
You can rank by group, or use a sub-query.
It is easier if you have a primary key: pk
Ranking by group:
-------------------------------------------
SELECT a.concatenation, a.pk,
LAST(a.month), LAST(a.year), LAST(a.vendor), LAST(a.mmc),
LAST(a.sales)
FROM table AS a INNER JOIN table AS b
ON a.concatenation = b.concatenation
AND a.sales >= b.sales
AND a.pk >= b.pk
GROUP BY a.concatenation, a.pk
HAVING COUNT(*) <= 10
ORDER BY a.concatenation, LAST(a.sales) DESC
--------------------------------------------
Using sub-query:
------------------------------------------
SELECT concatenation, pk, month, year, vendor, mmc, sales
FROM table AS a
WHERE pk IN( SELECT TOP 10 b.pk
FROM table AS b
WHERE b.concatenation = a.concatenation
ORDER BY b.sales DESC, b,pk)
ORDER BY concatenation, sales DESC
-------------------------------------------
(probably slower, though)
The last ORDER BY clause, ofeach of these queries, is just to get
a
nice
output, but that is not necessary to just 'get' the records.
Vanderghast, Access MVP
