is this a bad way to do percentile

[jd142]

Normally when people ask about getting the Nth percentile from a group,
the response is to write a couple of VBA modules. But it seems to me
there's a simpler way that just uses 2 queries. You might even be able
to get it down to one query if you nest them.

Here's how I get the 25th percentile number from a table, where
mynumber is the field that has the value and mytable is the table name:

top_25_query:
select top 25 percent mynumber from mytable order by mynumber;

25th_percentile_query:
select top 1 mynumber from top_25_query order by mynumber desc;

This seems more portable to me and easier for non-scripters to
understand.

Is there any reason that this would be a wrong way to do it? Will it
give me the wrong answer? It hasn't yet.

Thanks

[Randy Harris]

"jd142" <jd***@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Normally when people ask about getting the Nth percentile from a group,
the response is to write a couple of VBA modules. But it seems to me
there's a simpler way that just uses 2 queries. You might even be able
to get it down to one query if you nest them.

Here's how I get the 25th percentile number from a table, where
mynumber is the field that has the value and mytable is the table name:

top_25_query:
select top 25 percent mynumber from mytable order by mynumber;

25th_percentile_query:
select top 1 mynumber from top_25_query order by mynumber desc;

This seems more portable to me and easier for non-scripters to
understand.

Is there any reason that this would be a wrong way to do it? Will it
give me the wrong answer? It hasn't yet.

Thanks

What if there is more than 1 record with the 25th percentile mynumber?

[jd142]

Since I only what the number, not who has that number, it shouldn't
make a difference.

In other words, I don't care that on their final exam, John, Jane,
Richard, and Alice all scored a 32, I just care that 32 is the score at
the 25th percentile.