Limit option suggestion

A suggestion:

SQL databases often contain a WITH TIES, or TOP  clause. This works like 
LIMIT but interacts with the sort criteria  so that equal keys are also 
included at the cutoff point. This option only makes sense when orderby 
is specified.
E.g.

SELECT ?Salesman, ?Revenue
FROM ...
WHERE ...
ORDER BY ?Revenue DESC
LIMIT 5 WITH TIES

The following:

S1 10,000
S2 20,000
S3 22,000
S4 25,000
S5 19,000
S6 10,000
S7 5,000

Would produce six results due to ties in the 5th place:

S4 25,000
S3 22,000
S2 20,000
S5 19,000
S6 10,000
S1 10,000

Note that this does not permit any control of the ordering of S6 and S1. 
A more flexible scheme is possible:

SELECT ?Salesman, ?Revenue
FROM ...
WHERE ...
ORDER BY ?Salesman                   // The final ordering of the result
LIMIT 5 BY ?Revenue DESC         // The ordering used to select the 
LIMIT candidates.

Of course this involves a double sort - typically the second sort is 
small. This is implemented as the TOP clause in MS Access and SQL 
Server. The lack of the second syntax form is often a problem in 
applications.

Received on Thursday, 21 April 2005 23:29:56 UTC