yes, of course I understand it's not easy to understand.

Indeed, Ken, you're close to what I had in mind, but the thing is that SQL servers keeps telling me that ROW_NUMBER isn't a valid function.

When I make it rank=count(*) SQL server struggles with the OVER keyword. I work via a SQL server management studio express (2005) to a sql server (hosting at a hostingcompany) with version 8.0.2039.

Shortly:

There is a table called stm_subscription, containing all subscriptions for another table stm_sessie (containing some camp information).

Each camp has a maximum number of participants (lets say 50 for example). People must be able to subscribe continously without that limit. The application must be smart enough to determine if the member (= the one who is subscribing to a camp) is enlisted in a reserve list or not.

How can I determine who's first? Well, the subscription table contains a created_date field (up to seconds). Via this I can easily request a complete list of all participants, ordered by subscription date.

Now, when the 51th participant (member) wishes to subscribe, this person must be treated the same way as all other subscriptions, but he/she should be visualized in another list on screen. Problem is that the query is built dynamically in code (via searchcriteria). When the searchcriteria contains "showOnlyReserve", it means that I only want to see those participants where:

1. the number of subscriptions is larger than the number of maximum allowed participants

2. in our case: I want to see the 51th (and higher, 52th, 53th ...) participant

For the first one I use following in the where clause:

(SELECT count(r.id) FROM STM_SUBSCRIPTION r WHERE r.sessie_id = e.id) > e.max_number_participants

Via that query I get to see only those camps which have more subscriptions that allowed,

But this gives me ALL participants. The big question is: how can I get only the ones that are reserve - knowing that the only thing you can use is the creation_date.

I thought that, when I add a rownumber, I could then use that rownumber in my query to see if it is bigger than the max_number_participants, but I can't get it to work. So Ken's solution seems close. I've used Microsofts solution as well (http://support.microsoft.com/kb/186133), but the rank numbers I see don't make any sense.

I could send you (by mail) the complete query perhaps?

Thanks for your help,

Christophe