Top N values for each group in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

I am trying to select the top 25 records grouped by each product from an
underlying large table (100,000+ rows) in Access XP.

The KB article descibes how to do it but it takes several minutest to run on
a very modern PC. http://support.microsoft.com/?kbid=210039


Can anyone help pelase? Thanks in advance.
FJK
 
The only thing that will help would be to make sure you have an index on the
product field and whatever field(s) you are sorting by in the sub-query.

Beyond that you may have to live with the speed.
 
Hi,


*IF* you have MS SQL Server 2005 (which is still in beta), any version
(including the one that would be free).


1- in MS SQL Server 2005, create a database, then, in this database, create
a linked server toward your Jet database. Many possibilities, the Transact
SQL way to do it is to run the following:


-----------------------
sp_addlinkedserver 'AccessDb1', ' ', 'Microsoft.Jet.OLEDB.4.0',
'C:\MyFullPath\db1.mdb'
----------------------




Dependant of the tools you may have, you may have a User Interface about
adding a "linked" server.



2- In your Jet database, create the 'Pass-Through' (see help file for
details) toward the MS SQL Server 2005 database, with the following SQL
statement:

--------------------------------
WITH x (r, f1, f2, f3) As (SELECT RANK() OVER( ORDER BY f2, f3 DESC) AS r,
f1, f2, f3
FROM AccessDB1...Table1)

SELECT MAX(y.f1), y.f2, y.f3
FROM x As y INNER JOIN x As z ON y.f2=z.f2
GROUP BY y.r, y.f2, y.f3
HAVING y.r BETWEEN MIN(z.r) AND 4+MIN(z.r)
--------------------------------


where the magic number 4 comes from the fact you want the top 5, per
group.


In MY CASE, table1 fields are as follow:
f1 is the pk, and not really used, except for supplying reference,
f2 is the real group (as example, the country)
f3 is the value ranking the individuals




The working idea is to rank everyone, by group, f2, by value, f3,
DESCreasingly. Unfortunately, that makes values from 1 to N, where N is the
total number of records. On the other hand, we can spot each MINIMUM rank,
for a given value of f2, and thus, we can keep the ranks between these min,
and 4+these min.


Sure, that features two new techniques (CTE and RANK) only available in MS
SQL Server 2005. MS SQL Server 2000 won't do. Since the technique also use
linked server and pass-through query (I presumed, on the same PC), that is
transparent to your end user, while it seems everything still occur under
Access in Jet database. I haven't tested for 100K records, but I assume the
ranking feature of MS SQL Server is more preferment than the traditional
inner join made to obtain the rank.





Hoping it may help,
Vanderghast, Access MVP
 
Hi,


Well, a much better way is to use APPLY, also a new feature in MS SQL Server
2005:


1- As before

1b- Add a function in the MSQL Server database:

CREATE FUNCTION Top5Given( @param varchar(50) )
RETURNS TABLE
RETURN SELECT TOP 5 *
FORM AccessDB1...Table1
WHERE f2=@pamam



2- The Pass-Through query, in Access:

WITH x(f2) AS (SELECT DISTINCT f2 FROM AccessDB1...Table1)
SELECT x.f2, p.f1, p.f3
FROM x CROSS APPLY Top5Given( x.f2) As p
ORDER BY x.f2, p.f3 DESC



Here, the idea is to consider each record from x, then compute the APPLIED
function, and make a partial result... and continue with the next record of
x.


Not really applicable here, but for completeness, in cases the APPLIED
function would return no record, but that you wish to keep the record_x, use
OUTER APPLY.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top