SELECT TOP 50 PERCENT for group?

[mrubel99]

Does anyone know how I would modify this query so that it will return the TOP 50% FAC_METRIC values for each PROJECT_NAME?

I have read a few posts about using a subquery as criteria for field FAC_METRIC?

But, I can't seem to make it work???? Thanks!

SELECT qryQuartileStep3a.PROJECT_NAME, qryQuartileStep3a.SYSTEM_FACILITY, qryQuartileStep3a.FAC_METRIC
FROM qryQuartileStep3a
ORDER BY qryQuartileStep3a.FAC_METRIC;

[Stewart Ross]

Hi. There is no direct way to return a percentage of rows in SQL. You may be confusing the SELECT TOP N predicate in SQL. TOP N just returns the specified number of rows (if they exist); it does not select the TOP N rows within a grouping, nor does it handle percentages.

To return a percentage of the rows you would need to know the count of the number of rows for each group within your query. You can do this as a separate Count query, but what you do after that could get quite complex in SQL. I can't think of a direct approach using subqueries which would help you either.

This is really a job for Excel... something I find myself saying fairly frequently now in response to posts of this nature. Databases are good at data handling and organisation, particularly in a multi-table multi-relation context, but they are not anywhere near as strong when it comes to flexible statistical analysis as Excel is. Export the data to Excel and let it do the statistical analysis. As an example of the difference, just try finding the median* value of a particular group in Access through application of SQL queries alone and you will see what I mean...

-Stewart

*median is the mid-point value in a set of ordered numeric data

[mrubel99]

Hi Stuart,

Thanks once again for the response. Like you suggest, I am currently dumping the data into excel to run the quartile calculation and then bringing the results back into the database, it works, but its a little clunky.

I have been working on trying to find a more "dynamic" solution for this quartile calculation. I have been able to build a series of six queries that will work for a single series, but it is terribly slow, and will not work with multiple data groups. There may be some hope as SQL 2005 apparently has some upgraded functions including Row_Number which could turn out promising, our upgrade is pending, so we'll have to see.

Anyway, I thought I would throw this out there to see if anyone had any tricks to share to get this done.

Thanks again.

Mike