Top Select returning Ties

[newkid via AccessMonster.com]

Hi All,

Please find the time to help me out - I have the following query:
----------------------------------
SELECT TR.[Rep ID], TR.[SumOfVoice GA], TR.[SumOfData GA], TR.[Total GA], TR.
[MSD Code], TR.[Primary]
FROM zz_ReportingSummary AS TR
WHERE (((TR.[Total GA]) In (SELECT TOP 10 [Total GA]
FROM zz_ReportingSummary
WHERE [Rep ID] = TR.[Rep ID]
ORDER BY [Rep ID],[Total GA] DESC)))
ORDER BY [Rep ID], [Total GA] DESC , TR.[MSD Code], TR.[Primary];
-----------------------------------
Primary is an auto number feild that i used as the primary key
MSD code is also unique for 99% of the records

My issue is I only need the top 10 Total GA, regardless of ties - there arent
any business rules for ties, I just need the highest ten and dont care about
the ties in 10th place. I know your suppose to order by the primary key to
solve this issue and I have, yet it still isnt working.

Pardon my coding this is the first time I've written code based on what I've
seen online.
Thanks

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called TopQuery" which illustrates how to deal with duplicates in a
Top query. You can find it here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=233

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Spencer MVP]

This should select the top 10 for each Rep ID

SELECT TR.[Rep ID]
, TR.[SumOfVoice GA]
, TR.[SumOfData GA]
, TR.[Total GA]
, TR.[MSD Code]
, TR.[Primary]
FROM zz_ReportingSummary AS TR
WHERE TR.Primary In
(SELECT TOP 10 [Primary]
FROM zz_ReportingSummary
WHERE [Rep ID] = TR.[Rep ID]
ORDER BY [Rep ID],[Total GA] DESC, Primary)
ORDER BY [Rep ID], [Total GA] DESC , TR.[MSD Code], TR.[Primary];

If you want the top 10 per Rep ID you really don't need to sort by REP ID in
the sub-query since you have already selected only one rep id based on the
where clause.

If you want the top ten without regard for the REP ID, then change the
subquery to

SELECT TOP 10 [Primary]
FROM zz_ReportingSummary
ORDER BY [Total GA] DESC, Primary)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[newkid via AccessMonster.com]

THANKS GUYS!!!!!!!!!

So in the sub query I was suppose to use top 10 primary - I see it does the
sub query after the main query.
Thanks again.

This should select the top 10 for each Rep ID

SELECT TR.[Rep ID]
, TR.[SumOfVoice GA]
, TR.[SumOfData GA]
, TR.[Total GA]
, TR.[MSD Code]
, TR.[Primary]
FROM zz_ReportingSummary AS TR
WHERE TR.Primary In
(SELECT TOP 10 [Primary]
FROM zz_ReportingSummary
WHERE [Rep ID] = TR.[Rep ID]
ORDER BY [Rep ID],[Total GA] DESC, Primary)
ORDER BY [Rep ID], [Total GA] DESC , TR.[MSD Code], TR.[Primary];

If you want the top 10 per Rep ID you really don't need to sort by REP ID in
the sub-query since you have already selected only one rep id based on the
where clause.

If you want the top ten without regard for the REP ID, then change the
subquery to

SELECT TOP 10 [Primary]
FROM zz_ReportingSummary
ORDER BY [Total GA] DESC, Primary)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi All,

[quoted text clipped - 20 lines]

seen online.
Thanks