Select TOP 0.1 Percent

[Lali]

I have a table of 8728 records, but I only want to do calculations for the bottom 99.9%, i.e. I can exclude the top 0.1%
I know I can't use 99.9 or 0.1 in the Select statement as TOP needs to be followed by an INT

So, I have calculated the number of records I want to use
(Round([NOf_East_Jitter]*0.999) AS CNOR which comes to 8719

But how do I put this calculated value into a Select statement?

 

[wiklendt]

have you tried using the query grid with the following?

CNOR: (Round([NOf_East_Jitter]*0.999)

note: how have you generated [NOf_east_jitter]?

 

[Lali]

Hi,
My initial query is a make table to store results

SELECT Count(*) AS AllCount, Round(Count*0.999) AS ReportCount, Avg([1RawJitter].[Jitter]) AS AllAvg, 0 AS ReportAvg INTO 2RptJitter
FROM [1RawJitter];

Then I want to do a SELECT TOP (2Jitter.ReportAvg) type query to update 2Jitter.ReportAvg to contain the AVG value of the bottom 99.9% of results. This is where I have problems!

 

[prley]

I too am looking for a top / bottom select statement to show the last number of records similiar to what mysql has. I always can do it through programming but would pefer a query.

 

[prley]

Nevermind I refined my search and found that the Top example here.

http://www.access-programmers.co.uk/forums/showpost.php?p=328277&postcount=1

 

[Banana]

Just for reference:

One possible solution in pure SQL to get 0.1%:

SELECT TOP 10 PERCENT * FROM
(SELECT TOP 1 PERCENT * FROM sometable) t;

Not so efficient. An alternative syntax is to take advantage of the autonumber:

SELECT * FROM 
sometable t WHERE 
    (SELECT Count(id) FROM 
    sometable c WHERE c.id <= t.id) 
    = (SELECT 0.01 * Count(id) FROM sometable);

HTH

 

[prley]

Just for reference:

One possible solution in pure SQL to get 0.1%:

SELECT TOP 10 PERCENT * FROM
(SELECT TOP 1 PERCENT * FROM sometable) t;

Not so efficient. An alternative syntax is to take advantage of the autonumber:

SELECT * FROM 
sometable t WHERE 
    (SELECT Count(id) FROM 
    sometable c WHERE c.id <= t.id) 
    = (SELECT 0.01 * Count(id) FROM sometable);

HTH

That second example looks pretty slick.