Post reply

reset sequence when value of one column changes

  • August 13, 2008 at 6:48 am

    #72710

    I have trouble to write the select statement to reset the sequence number when value of one column changed. I don't want to use cursor if possible.

    Example

    Table - Course

    Courseid,

    Coursename

    Table - ClassSchedule

    ClassScheduleid

    ClassSchedueTime

    Courseid

    INSERT INTO Course(Courseid, CourseName)

    SELECT 1, 'Math101'

    UNION

    SELECT 2, 'Math102'

    INSERT INTO ClassSchedule(ClassScheduleid, ClassScheduleTime, Courseid)

    SELECT 1, '09:00am', 1

    UNION

    SELECT 2, '11:00am', 1

    UNION

    SELECT 3, '02:00pm', 1

    UNION

    SELECT 4, '08:00am', 2

    UNION

    SELECT 5, '01:00pm', 2

    UNION

    SELECT 6, '02:00pm', 2

    UNION

    SELECT 7, '03:00pm', 2

    I want to result in courseid, classscheduleTime order and assign a sequence number.

    Courseid, ClassSchedule, Sequence

    1, '09:00AM', 1

    1, '11:00AM', 2

    1, '02:00PM', 3

    2, '08:00AM', 1

    2, '01:00PM', 2

    2, '02:00PM', 3

    2, '03:00PM', 4

    Thanks

  • August 13, 2008 at 7:22 am

    #857394

    Loner

    If you are using 2005 there is a built in function to do this:

    SELECT ClassScheduleid

    , ClassScheduleTime

    , Courseid

    , RANK() OVER (PARTITION BY Courseid ORDER BY ClassScheduleTime) AS [Rank]

    FROM dbo.ClassSchedule

    ORDER BY Courseid

    , ClassScheduleTime

    However, since this is posted in the 2000 section I'll assume that is what you are working with 😀 The following is a quick down and dirty routine which should do what you need though probably won't win any prizes for efficiency 😛

    SELECT c.ClassScheduleid

    , c.ClassScheduleTime

    , c.Courseid

    , (SELECT COUNT(*)

    FROM dbo.ClassSchedule

    WHERE Courseid = c.Courseid

    AND ClassScheduleTime < c.ClassScheduleTime) + 1 AS [Rank]

    FROM dbo.ClassSchedule c

    ORDER BY c.Courseid

    , c.ClassScheduleTime

  • August 13, 2008 at 7:57 am

    #857422

    It is SQL Server 2000 so I cannot use RANK, that is why I posted the question in this forum.

    The one you posted for SQL Server 2000 worked liked a charm.

    Thanks

  • August 17, 2008 at 7:00 pm

    #859222

    Loner (8/13/2008)

    It is SQL Server 2000 so I cannot use RANK, that is why I posted the question in this forum.

    The one you posted for SQL Server 2000 worked liked a charm.

    Thanks

    That's a ticking time bomb in the face of scalability. It's known as a "Triangular Join" and you need to read about how bad they are... they're worse than a cursor and can sometimes be millions of times worse than a cursor if any decent rowcount is involved! Please read the following URL about Triangular Joins...

    http://www.sqlservercentral.com/articles/T-SQL/61539/

    Then, take a gander at how to do ranking in SQL Server 2000 in the following article... it takes a bit to setup properly, but it'll save your life as a developer when the rowcounts start to get large...

    http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 18, 2008 at 5:15 am

    #859365

    Jeff,

    That's a ticking time bomb in the face of scalability. It's known as a "Triangular Join" and you need to read about how bad they are...

    How to find whether our code comes under 'Triangular Join' ?

    I have seen most of times, whenever you see any code at first sight, you are saying easily 'It is using Triangular Join'. How you are finding that ?

    If you tell (teach ) how to know whether the code is using Triangular Join or not, it would be very useful for me ( your student).

    karthik

  • August 18, 2008 at 5:35 am

    #859372

    Easy.

    Look at the

    1) JOIN arguments

    2) WHERE arguments

    3) Correlated subqueries arguments

    Equal character "=" only are most often OK, but the character combinations to look for are mostly

    >

    >=

    <

    <=

    <> or !=

    Haven't you read Jeff's article yet?

    There are even some small nice graphs illustrating the each and one scenario with the comparisons above.

    N 56°04'39.16"
    E 12°55'05.25"

  • August 18, 2008 at 5:15 pm

    #859737

    karthikeyan (8/18/2008)

    Jeff,

    That's a ticking time bomb in the face of scalability. It's known as a "Triangular Join" and you need to read about how bad they are...

    How to find whether our code comes under 'Triangular Join' ?

    I have seen most of times, whenever you see any code at first sight, you are saying easily 'It is using Triangular Join'. How you are finding that ?

    If you tell (teach ) how to know whether the code is using Triangular Join or not, it would be very useful for me ( your student).

    Basically, Peso is correct... if it contains a correlated sub-query with an inequality and nothing else to resolve the join, it's a triangular join.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 18, 2008 at 5:16 pm

    #859739

    ... you did read the article on Triangular Joins, didn't you? That's how you recognize them.

    http://www.sqlservercentral.com/articles/T-SQL/61539/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 18, 2008 at 5:36 pm

    #859745

    Peso (8/18/2008)

    Haven't you read Jeff's article yet?

    There are even some small nice graphs illustrating the each and one scenario with the comparisons above.

    Thanks for the plug, Peter...:)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 19, 2008 at 7:36 am

    #860012

    You're welcome.

    I forgot to mention

    BETWEEN

    NOT BETWEEN

    as two more potential "triangular join" triggers.

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply