SQL Question - TopN

SQL Question - TopN

Post by Peter Grossinge » Mon, 12 May 1997 04:00:00



Is there are way to send a SQL statement to a Paradox table where I only
want to have returned the topN, where N is a user-defined parameter.

Specifically, I am trying to group on a column, sum another column, then
display the groups using the sum column as the sort order, but I want to
restrict it to only the N items.

Something like select state, sum(population) S from mytable
group by state
order by S desc
"where record count is <=5" for example

TIA

Pete Grossinger

 
 
 

SQL Question - TopN

Post by Marc Scheun » Fri, 16 May 1997 04:00:00



>Is there are way to send a SQL statement to a Paradox table where I only
>want to have returned the topN, where N is a user-defined parameter.

Not in any standard SQL, no. There are a number of vendor-specific
extensions, but they all have their shortcomings, most notably
non-portability.

Marc

----------------------------------------------------------------------


 
 
 

SQL Question - TopN

Post by Peter Grossinge » Sat, 17 May 1997 04:00:00




> >Is there are way to send a SQL statement to a Paradox table where I
> only
> >want to have returned the topN, where N is a user-defined parameter.

> Not in any standard SQL, no. There are a number of vendor-specific
> extensions, but they all have their shortcomings, most notably
> non-portability.

> Marc

> ----------------------------------------------------------------------



 Well, how about setting a filter in a TTable which will only return N
records? Similar to the clause FOR RECNO() <= 10 in dBase?

Pete Grossinger

 
 
 

SQL Question - TopN

Post by Marc Scheun » Wed, 21 May 1997 04:00:00




>> >Is there are way to send a SQL statement to a Paradox table where I
>> only
>> >want to have returned the topN, where N is a user-defined parameter.

> Well, how about setting a filter in a TTable which will only return N
>records? Similar to the clause FOR RECNO() <= 10 in dBase?

First advice: DON'T USE TTABLE WITH SQL TABLES! TTable has quite a
number of drawbacks in terms of performance and speed, TTable is
intended for PC-/file-based systems like dBase and Paradox, but true
blue SQL is quite a different beast!

As I said, there are a number of ***vendor-specific*** extensions that
might allow what you want to do, such as the ROWNUM function in Oracle
(SELECT * FROM TABLE WHERE ROWNUM <= 10), but these are
***vendor-specific*** and therefore not applicable on every SQL
database server. so be aware of it when you use them!

Marc

----------------------------------------------------------------------

 
 
 

1. SQL question - TopN

Is there are way to send a SQL statement to a Paradox table where I only

want to have returned the topN, where N is a user-defined parameter.

Specifically, I am trying to group on a column, sum another column, then

display the groups using the sum column as the sort order, but I want to

restrict it to only the N items.

Something like select state, sum(population) S from mytable
group by state
order by S desc
"where record count is <=5" for example

TIA

Pete Grossinger

2. How to solve being dumped out to $ prompt from within Progress. Related to interrupt key.

3. See how you compare to our Topn Ten List of MS SQL Server Database Administrators

4. Creating Standby DB From Backup

5. Topn query doesnt work in PL/SQL

6. To fragment or not to fragment - opinions requested

7. Topn, Top n%, and Page n of n

8. Update question and SQL question

9. SQL Query question and unload db question

10. Newbie question... ODBC and SQL server 6.5 Question

11. Access question -> SQL question