Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


rownumber


rownumber

Author
Message
christophe-590358
christophe-590358
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
Hi,

I have a number of tables that are used for member subscription to a number of camps.
We know the max number of possible subscriptions (i.e. 50 places).
For every subscription we remember the datetime of it.

How can I find out the following:

1. get only the optional subscriptions (in this case: all subscriptions above 50).
2. get only the effective subscriptions (<= 50)

I don't have a flag that states that a given subscription is for the "reserve" list.
I only have the datetime. So if you order by the created_date field you'll see all subscriptions for a given camp.
Could you help me find out how to get the 2 above mentioned lists?

This is the query so far (the one returning ALL subscriptions where a camp has reached its limit.

SELECT DISTINCT
a.id, a.member_id, c.related_id, a.sessie_id, a.created_date
FROM ideefiks.STM_SUBSCRIPTION a
LEFT OUTER JOIN ideefiks.STM_SESSIE e ON a.sessie_id = e.id
LEFT OUTER JOIN ideefiks.STM_MEMBER c ON a.member_id = c.id
WHERE
--this where clause makes sure I only get those subscriptions from which the camp has reached its limit.
(SELECT count(r.id) FROM STM_SUBSCRIPTION r WHERE r.sessie_id = e.id) > e.max_aantal_deelnemers
group by a.id, a.member_id, c.related_id, e.id, c.id, c.verwantschap_id, e.kamp_id, a.sessie_id, a.created_date
ORDER BY a.created_date


This is a small extraction of the result (session_id = the camp).
subId member_id related_id ses_id created_Date
2299 3082 3081 55 2008-01-05 10:30:53.930
2300 3083 3081 55 2008-01-05 10:30:54.353
2304 3092 3091 55 2008-01-05 12:09:53.517
2305 3093 3091 55 2008-01-05 12:09:53.923
2344 3165 3163 55 2008-01-06 12:32:07.810
2345 3166 3163 55 2008-01-06 12:32:08.170
2399 3220 3219 55 2008-01-06 20:03:52.967
2472 3306 3305 55 2008-01-07 11:15:42.870
2480 3321 3319 55 2008-01-07 12:26:50.390
2557 3431 3430 55 2008-01-07 21:01:29.580
2739 1132 1129 55 2008-01-09 07:02:36.020
2780 3705 3704 55 2008-01-09 11:18:06.517

Thank you for the help!

Christophe
Ken McKelvey
Ken McKelvey
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 Visits: 7295
Without DDL and test data it is difficult to know what you want.
Maybe you need to use the ROW_NUMBER() function like:

SELECT *
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY S.sessie_id ORDER BY S.created_date) AS Row
        ,S.*
    FROM ideefiks.STM_SUBSCRIPTION S
) a
    LEFT JOIN ideefiks.STM_SESSIE e
        ON a.sessie_id = e.id
    LEFT JOIN ideefiks.STM_MEMBER c
        ON a.member_id = c.id
WHERE a.Row <= e.max_aantal_deelnemers
ORDER BY
a.created_date


Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
I am not entirely sure I follow what you are after. This article will provide you some help tips on how to post your problem correctly, so members are more likely to assist in solving your problem. Not only will more people help you with your problem we will be able to better understand the issue at hand rather than making guesses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

1. get only the optional subscriptions (in this case: all subscriptions above 50).
2. get only the effective subscriptions (<= 50)

The simple answer would be to say the following, but I do not know enough about your problem to be accurate.

SELECT *
FROM TABLE
WHERE SUBSCRIPTIONS > 50

AND

SELECT *
FROM TABLE
WHERE SUBSCRIPTIONS <=50



My blog: http://jahaines.blogspot.com
christophe-590358
christophe-590358
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47427 Visits: 44405
christophe (1/10/2008)
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.


If you're using SQL 2000, you should rather post in the SQL 2000 forums. In the 2005 forums, people assume you're using SQL 2005 and will offer suggestions based on that.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search