rownumber

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » rownumber

rownumber

Rate Topic

Display Mode

Topic Options

Author

Message

christophe-590358

Posted Wednesday, January 09, 2008 9:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 07, 2011 4:08 AM
Points: 3, Visits: 9

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

Post #440750

Ken McKelvey

Posted Wednesday, January 09, 2008 10:10 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:33 AM
Points: 822, Visits: 5,102

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

Post #440786

Adam Haines

Posted Wednesday, January 09, 2008 6:18 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 2,278, Visits: 2,998

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

Post #440960

christophe-590358

Posted Thursday, January 10, 2008 12:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, January 07, 2011 4:08 AM
Points: 3, Visits: 9

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

Post #441022

GilaMonster

Posted Thursday, January 10, 2008 2:26 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:21 AM
Points: 37,686, Visits: 29,943

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 2008, MVP
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

Post #441049

« Prev Topic | Next Topic »

Permissions