How to select top25 of teams within table (not top25 of records) 2

Hi easyit,

Top 25 (or whatever number) applies to a complete query not a group within it, and I don't think there's any way round that. You can, however, with only seven teams, take the Union of each of seven separate Selects ..

SELECT TOP 25 ITEMS WHERE TEAM = 1
UNION
SELECT TOP 25 ITEMS WHERE TEAM = 2
UNION
SELECT TOP 25 ITEMS WHERE TEAM = 3
:
:
etc.

However, I'm unclear about what you really want. You say you have 20K Items but want to report on the Top 25 Customers by Team AND summarise the other Customers; it may all be a bit much for a single query.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I shall do this in three steps to make it easier to follow.
It is possible to merge all three steps into one but I'll leave that to you.

Create saved query Q1: (Gets top 25 per group)
SELECT 1 AS Gp, [Mytable].TeamID, [MyTable].MyValue AS Qty
FROM [MyTable]
WHERE ([MyTable].MyValue) In (Select top 25 t.MyValue from [MyTable] as t where [MyTable].Teamid = t.Teamid order by t.MyValue desc)
ORDER BY [MyTable].TeamID, [MyTable].MyValue DESC;

Create saved query Q2gets sum of the rest)
SELECT 2 AS gp, [MyTable].TeamID, Sum([MyTable].MyValue) AS Qty
FROM [MyTable] LEFT JOIN Q1 ON ([MyTable].TeamID = Q1.TeamID) AND ([MyTable].MyValue = Q1.Qty)
WHERE (((Q1.TeamID) Is Null))
GROUP BY 2, [MyTable].TeamID
ORDER BY [MyTable].TeamID, Sum([MyTable].MyValue) DESC;

Create query Q3: puts the results together:
(select * from q1)
UNION (select * from q2)
ORDER BY teamid, gp, qty DESC;

Hi,

Thank you both for the response. Tony, the table contains unpaid invoices and we have teams who are collecting them. It is very helpful to know who the top 25 customers within each team are, and how the amount due is related tot the rest of their customers. Each customer has several line items (= records) that make up one invoice, and of course they can have several invoices due.

I’m trying the nested queries first (as suggested from lupins46) then I will try the Union solution. I’ll keep you posted!

Maarten

Hi,

The lupins46 solution takes a very long time to run. I interupted the first query after 45 minutes and will keep that solution on hold for now.

The other suggestion is promising, but...

When queried seperately, the result(top5) should be:
Teams due
CORPORATE € 3.438.655,45
CORPORATE € 2.643.523,79
CORPORATE € 1.787.537,84
CORPORATE € 1.718.492,93
CORPORATE € 1.709.348,74
and:
Teams due
LARGE € 1.249.091,98
LARGE € 949.631,88
LARGE € 526.299,64
LARGE € 501.936,66
LARGE € 427.042,83

But the union query result is:
Teams due
LARGE € 1.249.091,98
LARGE € 949.631,88
LARGE € 526.299,64
LARGE € 501.936,66
LARGE € 427.042,83
CORPORATE € 57,42
CORPORATE € 136,00-
CORPORATE € 307,02-
CORPORATE € 455,86-
CORPORATE € 1.594,52-

This is the query:

SELECT TOP 5 TEST.DUE, Teams
FROM TEST
WHERE (((TEST.Teams) Like 'LARGE*'))
ORDER BY TEST.DUE DESC
UNION
SELECT TOP 5 TEST.DUE, Teams
FROM TEST
WHERE (((TEST.Teams) Like 'CORPORATE*'))
ORDER BY TEST.DUE DESC
;

the scond part isn't lookingup the top values within the team, but the first five records instead.

Am I overseeing something obvious here?

Hi easyit,

My apologies - it seems you cannot put an ORDER BY on each of multiple separate SELECTs in a UNION Query. I don't know why and, at the moment, I don't know an easy way round it, except to set up separate queries for the top 25 for each Team and then UNION all of them together.

Again, my apologies.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Sorry to hear about the timing issues.

There is no problem with multiple order bys; in fact they are essential.

(SELECT TOP 5 TEST.DUE, Teams
FROM TEST
WHERE (((TEST.Teams) Like 'LARGE*'))
ORDER BY TEST.DUE DESC)
UNION
(SELECT TOP 5 TEST.DUE, Teams
FROM TEST
WHERE (((TEST.Teams) Like 'CORPORATE*'))
ORDER BY TEST.DUE DESC)
Order By teams, Due desc

Hi lupins46,

I hate the effect parentheses have on Jet SQL, but I'm glad to see it can be done after all.

Thanks, and a star from me.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Correction--it's only the last subquery that gives a sorting order (and only the first query that gives an alias to columns). *This is the only time Access lets you sort by Alias rather than column name or column position.
[tt]
SELECT Categories.CategoryName as Cat, Categories.Description as notes
FROM Categories

union all
SELECT Categories.CategoryName, Categories.Description
FROM Categories
order by cat,notes[/tt]



Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

lupins46

There isn't a "problem" with multiple ORDER BYs ... its just that all but the last one are ignored.

You can also ORDER BY field position as in

ORDER BY 1, 2

Hi Quehay,

I'm not sure what you are correcting; lupins46's parenthesised subqueries with individual sort orders work just fine; he also specifies an overal sort order at the end, as you show.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Tony,

This was the statement:

"There is no problem with multiple order bys; in fact they are essential." [my emphasis]

Yeah it certainly doesn't hurt if they're there--they're just ignored (as you point out).

Cheers,

Jeff

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

Whoops that was golom who pointed out the harmless redundancy

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

...and Lupins was pointing out the essential need for an order by in any TOP n subquery to make the top meaningful rather than the arbitrary first n records to appear. Yikes

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

LOL Jeff - don't know where you are, but it's late at night here - it's easy to get mixed up when the posts come thick and fast

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I've just picked up the thread again.
Thank you Quehay, that is the issue I was raising.
A Top N query must have an order by clause if it is based on field values.

Hi,


I wasn't able to thank you for the responses. It was very helpfull and I learned a lot. Lupins, the star is deserved!

Maarten

Thank you

Just want to let Lupins know, I used your example to get top 5 profitable clients from each profit center and was successful. It worked great.

Thanks