Top 10

[billmark]

Hi
I've the SQL below and it gives me Top 11 but not Top10, please advise how to fix. Also, I would like to give them the no. of each record from 1 to 10.

QUOTE
SELECT TOP 10 tblSalesPayments.InvoiceNumber, tblSalesPayments.PaymentType, tblSalesPayments.PaymentAmount
FROM tblSalesPayments
ORDER BY tblSalesPayments.PaymentAmount DESC;
UNQUOTE


and I expect the result as follows:

Order InvoiceNumber PaymentType PaymentAmount
1 7 American Express $650.00
2 53 American Express $297.50
3 34 Check $250.00
4 7 American Express $200.00
5 42 Check $185.00
6 27 MasterCard $178.50
7 50 Check $171.80
8 34 American Express $160.25
9 45 Check $150.00
10 12 Check $150.00
44 Check $150.00 (this additional line should be removed from the Top 10)


Regards
Bill

 

[jdraw]

How would you decide on which record to drop?

If you don't want ties, then you have to identify how they will be resolved.

This is similar to PGA golf match

the winner has a score of 9 under par, next were 6 golfers at 7 under par, then 5 at 6 under par.

The rankings would be listed as

1.............................9 under par
T2 -A,B,C,D,E,F -------7 under par
T8 -G,H,I,J,K----------6 under par

 

[MarkK]

And since the sequential number does not actually exist in your data, (it is an after-the-fact reflection of your query's result), it is therefore most easily applied after-the-fact. For instance, if your query will appear in a report, program the report to count rows and display the sequence. Also, a recordset has an AbsolutePosition property that you can use, if you open a recordset. So think of the sequential number as a feature of your final presentation of the data, not a feature of the data itself, which is what the query renders.
Hope this helps,