Top 10 and Union Select

[VBALearner]

HI,

I am hoping to do this in SQL since I already have two subreports on my report and I have to do this to four reports total.

I need to do a top 10 for one company, a top 5 for a second company, a top 3 for a third company and a top 2 for a fourth company. Is it possible to do this in SQL? If so please help.

I tried this:

SELECT TOP 10 *
FROM [Table1] WHERE [Company Number]=1
ORDER BY Variance DESC
UNION SELECT TOP 10 *
FROM [Table1]
WHERE [Company Number]=2
ORDER BY Variance DESC;

What happens is that I get the right number of selections for each company but variances for the second company get skipped over.

For example if company 2 had a variance of 30% and company 1
10th variance was 5%, the variance for 30% will not show up.

 

[cheerio]

If you have a construction like:

select * from table1
union
select * from table2
order by zzz

I believe this is interpreted as

(
select * from table1
union
select * from table2
)
order by zzz

ie the order by is applied to the result of the union and not to table2.

On this basis you need some brackets to ensure that the order by is applied to table2.

What is actually happening to you is that your second selection has no ordering so you get the first 10 that Access finds which does not include the 30% variance.

Ken

 

[VBALearner]

Thank you so much.

It worked!!!!

I feel a little stupid. LOL. Live and Learn.