An aggregate type function query

[basildon]

I have a database of a couple of million and need to select the top 10,000 of these by turnover.

Is there a way of identifying these without much trial and error?

Thanks

 

[carp]

The solution will probably depend on your RDBMS. If you are using Oracle8.*, you can try

SELECT *
FROM (SELECT id, count(turnover_calculation) turnovers
FROM my_table
GROUP BY id
ORDER BY 2 DESC)
WHERE rownum < 10001;

 

[Tim1]

Hiya,

For MS SQL / Sybase, you could try:

SET ROWCOUNT 10000
GO

SELECT field1, field2, field3, turnover = SUM(field_to_be_summed)
FROM table
GROUP BY field1, field2 <--- This needs to be name, or id, however you want the sales
aggregated
ORDER BY turnover desc
Go

This will take a little while with 2,000,000 rows to process.

Tim