"SELECT TOP n" and "UNION"

[littlelegs]

I am hoping someone can help me with the following statement:

SELECT TOP 3 * FROM RIDSCORE WHERE ((RIDSCORE.RIDER_L) = "LEWANDOWSKI") And ((RIDSCORE.RIDER_F) = "Amanda") And ((RIDSCORE.LEVEL) = "B1" ) ORDER BY RIDSCORE.LEVEL UNION SELECT TOP 3 * FROM RIDSCORE WHERE ((RIDSCORE.RIDER_L) = "LEWANDOWSKI") And ((RIDSCORE.RIDER_F) = "Amanda") And ((RIDSCORE.LEVEL) = "B2" ) ORDER BY RIDSCORE.LEVEL ;

It returns ALL records from the first select but return ONLY the 3 records requested, from the 2nd select stmt...

If I comment out the first select and union statement, only fetching for the second select then it again returns all the records not just the first 3.

 

[David R]

(unhelpful advice deleted here, see below)

[This message has been edited by David R (edited 05-30-2002).]

 

[littlelegs]

I want to end up with a recset with 6 recs in it - 3 at one level and 3 at the next level. Wouldn't UNION first then SELECT TOP 3 give me a recset with only 3 recs?

Forgive me, if I am not seeing the obvious...

)

 

[David R]

Heh. My mistake, let's try that again.

Foolproof method: Make two SELECT TOP 3 queries and then UNION them together. There we go, horse before cart.

Union queries can only take a single ORDER BY statement, that is probably why you were having trouble before. However if you specified LEVEL = "B#" then what is the purpose of ORDER BY LEVEL in the SELECT statements?

[This message has been edited by David R (edited 05-30-2002).]

 

[littlelegs]

Thanks - I was going to reply that I needed to have the LEVEL ranked for the award but then realized it doesn't matter because of eligiblity checking earlier on.

So I will try to remove the ORDER BY stmt and see what happens - Thanks muchly!

 

[littlelegs]

Tried it and the sql is working much better, still have a few anomalies... but the results are more what I expected.
Thanks again...