Select first 50 rows of a sorted recordset by value desc.

[johnvai]

Dear Friends:

I have a question, which i am not sure if it works in sql.
i have member names in a table with their number of orders field.
MEMBERS
-------
ID
MEMNAME
NUMOFDEL

I want to show the first 50 members having the maximum number of orders (NUMOFDEL) grouped as well by MEMNAME or ID.
Example:
1 JOHN 500
2 DANY 490
3 MIRA 490
4 ISAAC 375
:
:
:
50 FRED 150

Thanks.

 

[r937]

select top 50 ID,MEMNAME,NUMOFDEL
from MEMBERS
order by NUMOFDEL desc

rudy

http://r937.com/

SQL Consulting

 

[johnvai]

Dear Rudy.

I just wanted to say that i tried the TOP function but its not working in Sybase SQL. why? I don't know.
Any other suggestions? (Sybase Adaptive Server V 5.05)

 

[PHV]

You're in a Microsoft Access forum.

 

[dhookom]

I'm seriously tempted to give PHV a star for this response.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[johnvai]

Well the forum is access queries and "JET SQL" the jet sql does not stand for only access guys!
if you go just a bit farther in ur researches, u can find that out!

thanks for the help.

 

[PHV]

Jet SQL supports the TOP predicate.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

I did a search in google and found a reference that might help:

From the help file:

Adaptive Server Anywhere SQL User's Guide
7. Summarizing, Grouping and Sorting Query Results
The ORDER BY clause: sorting query results
Retrieving the first few rows of a query

You can limit the results of a query to the first few rows returned using
the FIRST or TOP keywords. While you can use these with any query, they are
most useful with queries that use the ORDER BY clause.
Examples

The following query returns information about the first employee sorted by
last name:
SELECT FIRST *
FROM employee
ORDER BY emp_lname

The following query returns the first five employees sorted by last name
comes earliest in the alphabet:
SELECT TOP 5 *
FROM employee
ORDER BY emp_lname

Restrictions on use of FIRST and TOP
FIRST and TOP are supported in the outermost SELECT block of a request. They
should be used only in conjunction with an ORDER BY clause to ensure
consistent results. FIRST is also supported in a subqueries that are either
in a query's SELECT list, or are involved in a comparison predicate-the
subquery is not part of a quantified predicate involving IN, ANY, SOME, or
ALL.

For example, the nested query
SELECT *
FROM sales_order_items
WHERE prod_id = (SELECT FIRST from product)

is supported, whereas

SELECT *
FROM sales_order_items
WHERE prod_id = ANY(SELECT FIRST from product)
is not.
Unsupported instances of FIRST or TOP n may not trigger a syntax error, but
will likely yield unexpected or unpredictable results. For this reason you
should refrain from using FIRST or TOP in SQL constructs other than the two
mentioned above. Specifically you should not specify FIRST or TOP in a
derived table, view, or quantified subquery.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[johnvai]

Thanks Dhookom for your help.
I already tried it (top) but it gives me "syntax error near 5", when executing the folloiwing:

SELECT TOP 5 *
FROM employee
ORDER BY emp_lname

any hints?

 

[r937]

sybase adaptive server version 5?

isn't that a bit old?

i don't really know, i've never used sybase, but taking your suggestion ("if you go just a bit farther in ur researches, u can find that out!") i did a little digging on the sybase web site, and it looks like they're on version 12 now

so my guess is that TOP isn't supported in version 5

would you be willing to accept a solution that involves a theta self-join?

(if you don't know what a theta self-join is, johnny dear, it's time for you to do some research_




rudy

http://r937.com/

SQL Consulting

 

[johnvai]

Dear Rudy I have browsed your web site and i already have programs working on sybase 5 and cant update to 12 for all my clients.
I thought i can get some help solving within my situation.

thanks anyways.