Sequentially Numbering Groups of Records

13 Sep 2006 3:38 PM

Anonymous

How do I sequentially number groups of records from a table in SQL Server
2000 based on a date field and OrderID. I would like to update the
SequenceID field in the table once I determine the sequential numbering.

Table:
Name,
Date,
OrderID,
SequenceID (Update this field)

Check Date first and if Date are the same check OrderID

Results:
Name                 Date                   OrderID               SequenceID
John Doe            01/01/06             12                       1
John Doe            10/15/06             3                         2
John Doe            10/15/06             7                         3

Jane Doe           02/01/06              2                         1
Jane Doe           02/01/06              4                         2
Jane Doe           09/21/06              6                         3
Jane Doe           10/01/06              1                         4

Thanks!

13 Sep 2006 4:22 PM

markc600

update MyTable
set SequenceID = (select count(*)+1 from MyTable t2
                  where t2.Name=MyTable.Name
                    and ((t2.Date < MyTable.Date)
                     or (t2.Date = MyTable.Date
                      and t2.OrderID<MyTable.OrderID)))

13 Sep 2006 4:42 PM

Aaron Bertrand [SQL Server MVP]

Why would you want to store the rank in the table? This means you will have
to update and maintain it every time there is an insert/update/delete.

Since you can calculate this data when you run the query, it makes little
sense to store it.

See this article for some examples:
http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html

Show quote

"Anonymous" <Anonym***@discussions.microsoft.com> wrote in message
news:407667C2-3660-469F-BDE7-34FE507A337C@microsoft.com...
> How do I sequentially number groups of records from a table in SQL Server
> 2000 based on a date field and OrderID. I would like to update the
> SequenceID field in the table once I determine the sequential numbering.
>
> Table:
> Name,
> Date,
> OrderID,
> SequenceID (Update this field)
>
> Check Date first and if Date are the same check OrderID
>
> Results:
> Name                 Date                   OrderID
> SequenceID
> John Doe            01/01/06             12                       1
> John Doe            10/15/06             3                         2
> John Doe            10/15/06             7                         3
>
> Jane Doe           02/01/06              2                         1
> Jane Doe           02/01/06              4                         2
> Jane Doe           09/21/06              6                         3
> Jane Doe           10/01/06              1                         4
>
> Thanks!
>
>
>
>
>

13 Sep 2006 6:10 PM

Anonymous

You are right if this was a table being updated. However, it is not and I
need to store the value because I want to reference the value. I understand
that I can calculate this data but I do have a logical reason for storing it.

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> Why would you want to store the rank in the table? This means you will have
> to update and maintain it every time there is an insert/update/delete.
>
> Since you can calculate this data when you run the query, it makes little
> sense to store it.
>
> See this article for some examples:
> http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html
>
>
>
>
>
>
> "Anonymous" <Anonym***@discussions.microsoft.com> wrote in message
> news:407667C2-3660-469F-BDE7-34FE507A337C@microsoft.com...
> > How do I sequentially number groups of records from a table in SQL Server
> > 2000 based on a date field and OrderID. I would like to update the
> > SequenceID field in the table once I determine the sequential numbering.
> >
> > Table:
> > Name,
> > Date,
> > OrderID,
> > SequenceID (Update this field)
> >
> > Check Date first and if Date are the same check OrderID
> >
> > Results:
> > Name                 Date                   OrderID
> > SequenceID
> > John Doe            01/01/06             12                       1
> > John Doe            10/15/06             3                         2
> > John Doe            10/15/06             7                         3
> >
> > Jane Doe           02/01/06              2                         1
> > Jane Doe           02/01/06              4                         2
> > Jane Doe           09/21/06              6                         3
> > Jane Doe           10/01/06              1                         4
> >
> > Thanks!
> >
> >
> >
> >
> >
>
>
>