Selecting Top AND Bottom values (1 Viewer)

X

x0reset

Registered User.

Local time

Today, 05:41

Joined

Nov 14, 2005

Messages

52

• Dec 22, 2005

• #1

Hi all! I have a tough problem I was hoping I could get some help with:

I would like to create a recordset based on the "middle" 50% of the data. I need to chop 25% off the top of the data, AND 25% off the bottom of the data.

Any ideas?

 

G

gromit

Registered User.

Local time

Today, 05:41

Joined

Nov 17, 2005

Messages

260

• Dec 23, 2005

• #2

Hi -

1) Create a query, Query1, to UNION both the top 25 and bottom 25 percents:

Code:Copy to clipboard

SELECT TOP 25 PERCENT *  FROM tblValues
ORDER BY tblValues.Value DESC;
UNION
SELECT TOP 25 PERCENT *  FROM tblValues
ORDER BY tblValues.Value ASC

2) Create a second query, using the IN predicate:

Code:Copy to clipboard

SELECT tblValues.ValueID,tblValues.Value 
FROM tblValues 
WHERE tblValues.ValueID Not in (Select ValueID from Query1);

I tried to combine these in a single query, but I don't think you are allowed to use TOP in a subquery.

hth,

- g

 

X

x0reset

Registered User.

Local time

Today, 05:41

Joined

Nov 14, 2005

Messages

52

• Dec 23, 2005

• #3

Thanks Gromit, great solution!

 

Last edited: Dec 23, 2005

G

gromit

Registered User.

Local time

Today, 05:41

Joined

Nov 17, 2005

Messages

260

• Dec 23, 2005

• #4

Glad to help