display top 5th customer

[asifhasan1312]

Can anyone please help me to write a query to display top 5th customer according to CreditLimit.. (in sports2000 db).

Thanks in advance..

 

[Rob Fitzpatrick]

Helping you is different from doing it for you and giving you the answer.

What have you tried so far? What problems have you experienced? Please post some code.

 

[asifhasan1312]

Sure Sir.
I have used sports2000.db.

FOR EACH customer
by CreditLimit DESC:
DISPLAY CustNum Name CreditLimit.
LEAVE.
END.

This is the query which will give me the customer who has highest CreditLimit.

1. Is there any way i can get same result with where clause?
2. is there any way i can get 5th highest CreditLimit?

 

[jdpjamesp]

You could set a counter and leave when it's 5. For example.

 

[TomBascom]

IT sounds like you are hoping from some sort of SQLish "top" clause to stick inside your WHERE.

Rule #1 -- Progress is not SQL.

Rule #2 -- The harder you try to make Progress into SQL the unhappier you will be.

If you are using FOR EACH logic then just do as Cringer says and insert a counter.

If you are using a QUERY you might want to show that code instead of an example with FOR EACH but you might also find the CACHE option interesting if you're trying to limit the size of the result set. I.e.

DEFINE { [ [ NEW ] SHARED ] | [ PRIVATE | PROTECTED ] [ STATIC ] }
QUERY query
FOR buffer-name [ field-list ] [ , buffer-name [ field-list ] ] ...
[ CACHE n ]

 

[asifhasan1312]

Thanks for your answers.

Actually, i was thinking like SQL there might be some 'TOP' clause or nested query concepts in progress also.
But i was wrong.

Thanks for clearing my doubt.

 

[rajendran]

def var i as int no-undo.
for each customer no-lock break by creditlimit desc:
i = i + 1.
if i = 5 then
display customer.name customer.creditlimit.
end.

you will get the 5th record .

 

[jdpjamesp]

Have a careful look - you won't ever get a response because of the "i = 1 + 1" line.

 

[rajendran]

Thanks Cringer. Edited the code.