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 10 (of 10 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy