Retrieving Row Numbers

Greetings,

Using SQL Server, I'd like to query a simple table. But I'd like one of the
columns returned to be the row number of that row (the row number would not
actually be stored in the table).

I searched the Web a little and found ROW_NUMBER(), however it requires an
OVER statement that doesn't make much sense to me. I simply want the number
of each row. I don't know why I need a PARTITION or an additional ORDER BY
inside of an OVER clause, and I certainly don't want to add additional
overhead or modify my existing query.

Is there a simple way to do this?

If it matters, here is my query without the row numbers:

SELECT CategoryGroups.Title AS [Group], Categories.Title AS Category,
SUM(ScheduledDeliveries.TargetDeliveries) AS [Total Deliveries]
FROM ScheduledDeliveries INNER JOIN Categories ON
ScheduledDeliveries.CategoryID = Categories.ID
INNER JOIN CategoryGroups ON Categories.CategoryGroupID = CategoryGroups.ID
WHERE ScheduledDeliveries.ContractID = @ContractID
GROUP BY CategoryGroups.Title, Categories.Title

Thanks!

Jonathan

I know I'm not answering your question, but why do you want row numbers? You
should keep track of rows by using the primary key of the table, not some
arbitrary current row number. If you need a row number, add an identity
column to the table. Then it will always be there and never change.
I suspect that you enter row A, then B, then C, and expect SQL Server to
return A, B, C when you query the table. This is not true. It might easily
return A,C, B. That is, the order that the rows are returned, isn't static
unless you specify ORDER BY and sort by some column. And if that's a row
number, then it should be the primary key.
And your updates to the table should not walk through the table adjusting
row 1, then 2, then 3. That will kill your performance. Instead, update
based on primary keys. Your row numbers will have to be resolved to the
primary keys by SQL Server anyway. Or better yet, update whole sets of rows.
Of course I recognize that I have no idea what your business problem is, so
I hope you don't take this as an unwarranted flame mail.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Jonathan Wood" wrote in message
news:u8eWCg6AJHA.524-at-TK2MSFTNGP06.phx.gbl...
> Greetings,
>
> Using SQL Server, I'd like to query a simple table. But I'd like one of
> the columns returned to be the row number of that row (the row number
> would not actually be stored in the table).
>
> I searched the Web a little and found ROW_NUMBER(), however it requires an
> OVER statement that doesn't make much sense to me. I simply want the
> number of each row. I don't know why I need a PARTITION or an additional
> ORDER BY inside of an OVER clause, and I certainly don't want to add
> additional overhead or modify my existing query.
>
> Is there a simple way to do this?
>
> If it matters, here is my query without the row numbers:
>
> SELECT CategoryGroups.Title AS [Group], Categories.Title AS Category,
> SUM(ScheduledDeliveries.TargetDeliveries) AS [Total Deliveries]
> FROM ScheduledDeliveries INNER JOIN Categories ON
> ScheduledDeliveries.CategoryID = Categories.ID
> INNER JOIN CategoryGroups ON Categories.CategoryGroupID =
> CategoryGroups.ID
> WHERE ScheduledDeliveries.ContractID = @ContractID
> GROUP BY CategoryGroups.Title, Categories.Title
>
> Thanks!
>
> Jonathan
>

Rick,

>I know I'm not answering your question, but why do you want row numbers?
>You should keep track of rows by using the primary key of the table, not
>some arbitrary current row number.

A primary key would represent row numbers of all items in the table, not
just the ones matching the criteria in my query. Row numbers have many uses,
such as for paging. For my current need though, I'm simply trying to
duplicate some functionality of an existing system that shows week numbers
for the data displayed--this is what the client is used to. A table identity
would not provide this functionality. Also, my numbers must be sequential in
the order specified by the query, not by some arbitrary identity based on
the order rows were created.

> Of course I recognize that I have no idea what your business problem is,
> so I hope you don't take this as an unwarranted flame mail.

I don't mind being asked a question; however, if you are knowledgeable about
SQL and didn't think it worth answering the question as part of your reply,
you now have me thinking that there is no simple answer to my question. And
I don't understand why that is. I guess I can simply load the records into
an array and then number them myself. I would've just guessed this was a
very simple task.

Thanks.

Jonathan

For selecting data I would do it this way, creating a temporary table.
USE AdventureWorks
GO
SELECT IDENTITY(int, 1, 1) AS RowNumber, Name INTO #T1
FROM Production.Product
WHERE Name LIKE 'Chain%'
SELECT * FROM #T1
Of course you couldn't update the source table that way, because only your
temporary table has the row number.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

"Jonathan Wood" wrote in message
news:ec5Lbz6AJHA.2056-at-TK2MSFTNGP05.phx.gbl...
> Rick,
>
>>I know I'm not answering your question, but why do you want row numbers?
>>You should keep track of rows by using the primary key of the table, not
>>some arbitrary current row number.
>
> A primary key would represent row numbers of all items in the table, not
> just the ones matching the criteria in my query. Row numbers have many
> uses, such as for paging. For my current need though, I'm simply trying to
> duplicate some functionality of an existing system that shows week numbers
> for the data displayed--this is what the client is used to. A table
> identity would not provide this functionality. Also, my numbers must be
> sequential in the order specified by the query, not by some arbitrary
> identity based on the order rows were created.
>
>> Of course I recognize that I have no idea what your business problem is,
>> so I hope you don't take this as an unwarranted flame mail.
>
> I don't mind being asked a question; however, if you are knowledgeable
> about SQL and didn't think it worth answering the question as part of your
> reply, you now have me thinking that there is no simple answer to my
> question. And I don't understand why that is. I guess I can simply load
> the records into an array and then number them myself. I would've just
> guessed this was a very simple task.
>
> Thanks.
>
> Jonathan
>

Rick,

> For selecting data I would do it this way, creating a temporary table.
> USE AdventureWorks
> GO
> SELECT IDENTITY(int, 1, 1) AS RowNumber, Name INTO #T1
> FROM Production.Product
> WHERE Name LIKE 'Chain%'
> SELECT * FROM #T1

Hmm... I can research a bit, but I sure don't understand this. First,
perhaps it's my background that includes using languages such as assembler,
but creating a temporary table does not seem like is should be the most
efficient way to do what I need. Why SQL Server can't simply number the
results for me is a mystery.

Also, do I take this that you don't think much of ROW_NUMBER() in general,
or do you simply view it's use for a different purpose altogether?

Beyond that, I may have to dig around to fully understand some subtleties of
this query.

> Of course you couldn't update the source table that way, because only your
> temporary table has the row number.

Well, if I include a column that uniquely identifies the row in my final
SELECT then I should be able to.

Thanks for your input!

> "Jonathan Wood" wrote in message
> news:ec5Lbz6AJHA.2056-at-TK2MSFTNGP05.phx.gbl...
>> Rick,
>>
>>>I know I'm not answering your question, but why do you want row numbers?
>>>You should keep track of rows by using the primary key of the table, not
>>>some arbitrary current row number.
>>
>> A primary key would represent row numbers of all items in the table, not
>> just the ones matching the criteria in my query. Row numbers have many
>> uses, such as for paging. For my current need though, I'm simply trying
>> to duplicate some functionality of an existing system that shows week
>> numbers for the data displayed--this is what the client is used to. A
>> table identity would not provide this functionality. Also, my numbers
>> must be sequential in the order specified by the query, not by some
>> arbitrary identity based on the order rows were created.
>>
>>> Of course I recognize that I have no idea what your business problem is,
>>> so I hope you don't take this as an unwarranted flame mail.
>>
>> I don't mind being asked a question; however, if you are knowledgeable
>> about SQL and didn't think it worth answering the question as part of
>> your reply, you now have me thinking that there is no simple answer to my
>> question. And I don't understand why that is. I guess I can simply load
>> the records into an array and then number them myself. I would've just
>> guessed this was a very simple task.
>>
>> Thanks.
>>
>> Jonathan
>>
>

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

ROW_NUMBER() exists for just this purpose. My comment isn't meant to
disparage it. It's just that database centric people like me don't see much
utility in having an arbitrary number for a row. The primary key is the way
a database geek identifies a row. Using anything else inherently causes
difficulties and unnecessarily complicated manipulations.
You mention paging. I regard that as something that would be a feature of
the client application, not the database. After all, if two rows fit on a
page, would you have the database number every other row? That doesn't seem
like a good idea. What if one row has data that is exceptionally long and
takes two pages? Essentially, the page number is no more related to the data
that the font you use to display it.
So the basic idea here is to separate the data storage and manipulation (a
database responsibility) from the presentation (a client application
responsibility).

And yes. Include the primary key in the results and use that to update the
original record. Then you would have to recreate the temporary table if you
are trying to keep it up to date.
--
Rick Byham (MSFT), SQL Server Books Online
This posting is provided "AS IS" with no warranties, and confers no rights.

> "Jonathan Wood" wrote in message
> news:%23a$7PTHBJHA.1892-at-TK2MSFTNGP04.phx.gbl...
>> Rick,
>>
>>> For selecting data I would do it this way, creating a temporary table.
>>> USE AdventureWorks
>>> GO
>>> SELECT IDENTITY(int, 1, 1) AS RowNumber, Name INTO #T1
>>> FROM Production.Product
>>> WHERE Name LIKE 'Chain%'
>>> SELECT * FROM #T1
>>
>> Hmm... I can research a bit, but I sure don't understand this. First,
>> perhaps it's my background that includes using languages such as
>> assembler, but creating a temporary table does not seem like is should be
>> the most efficient way to do what I need. Why SQL Server can't simply
>> number the results for me is a mystery.
>>
>> Also, do I take this that you don't think much of ROW_NUMBER() in
>> general, or do you simply view it's use for a different purpose
>> altogether?
>>
>> Beyond that, I may have to dig around to fully understand some subtleties
>> of this query.
>>
>>> Of course you couldn't update the source table that way, because only
>>> your temporary table has the row number.
>>
>> Well, if I include a column that uniquely identifies the row in my final
>> SELECT then I should be able to.
>>
>> Thanks for your input!
>>
>>> "Jonathan Wood" wrote in message
>>> news:ec5Lbz6AJHA.2056-at-TK2MSFTNGP05.phx.gbl...
>>>> Rick,
>>>>
>>>>>I know I'm not answering your question, but why do you want row
>>>>>numbers? You should keep track of rows by using the primary key of the
>>>>>table, not some arbitrary current row number.
>>>>
>>>> A primary key would represent row numbers of all items in the table,
>>>> not just the ones matching the criteria in my query. Row numbers have
>>>> many uses, such as for paging. For my current need though, I'm simply
>>>> trying to duplicate some functionality of an existing system that shows
>>>> week numbers for the data displayed--this is what the client is used
>>>> to. A table identity would not provide this functionality. Also, my
>>>> numbers must be sequential in the order specified by the query, not by
>>>> some arbitrary identity based on the order rows were created.
>>>>
>>>>> Of course I recognize that I have no idea what your business problem
>>>>> is, so I hope you don't take this as an unwarranted flame mail.
>>>>
>>>> I don't mind being asked a question; however, if you are knowledgeable
>>>> about SQL and didn't think it worth answering the question as part of
>>>> your reply, you now have me thinking that there is no simple answer to
>>>> my question. And I don't understand why that is. I guess I can simply
>>>> load the records into an array and then number them myself. I would've
>>>> just guessed this was a very simple task.
>>>>
>>>> Thanks.
>>>>
>>>> Jonathan
>>>>
>>>
>>
>> --
>> Jonathan Wood
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>

Rick,

> ROW_NUMBER() exists for just this purpose. My comment isn't meant to
> disparage it. It's just that database centric people like me don't see
> much
> utility in having an arbitrary number for a row. The primary key is the
> way
> a database geek identifies a row. Using anything else inherently causes
> difficulties and unnecessarily complicated manipulations.
> You mention paging. I regard that as something that would be a feature of
> the client application, not the database. After all, if two rows fit on a
> page, would you have the database number every other row? That doesn't
> seem
> like a good idea. What if one row has data that is exceptionally long and
> takes two pages? Essentially, the page number is no more related to the
> data
> that the font you use to display it.

Okay, now you've really lost me. When we are talking about queries (we are),
I would think we are talking about the client application. The recommended
method of implementing paging in ASP.NET involves the use of ROW_NUMBER
(http://weblogs.asp.net/scottgu/archi...01/434314.aspx). So I'm just
not grokking your point here.

I should point out that the code at the link above also creates a temporary
table, as your example did. I'm just trying to make the point the row
numbers do seem to have a place in paging.

Thanks.

Jonathan

> --
> Rick Byham (MSFT), SQL Server Books Online
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>> "Jonathan Wood" wrote in message
>> news:%23a$7PTHBJHA.1892-at-TK2MSFTNGP04.phx.gbl...
>>> Rick,
>>>
>>>> For selecting data I would do it this way, creating a temporary table.
>>>> USE AdventureWorks
>>>> GO
>>>> SELECT IDENTITY(int, 1, 1) AS RowNumber, Name INTO #T1
>>>> FROM Production.Product
>>>> WHERE Name LIKE 'Chain%'
>>>> SELECT * FROM #T1
>>>
>>> Hmm... I can research a bit, but I sure don't understand this. First,
>>> perhaps it's my background that includes using languages such as
>>> assembler, but creating a temporary table does not seem like is should
>>> be the most efficient way to do what I need. Why SQL Server can't simply
>>> number the results for me is a mystery.
>>>
>>> Also, do I take this that you don't think much of ROW_NUMBER() in
>>> general, or do you simply view it's use for a different purpose
>>> altogether?
>>>
>>> Beyond that, I may have to dig around to fully understand some
>>> subtleties of this query.
>>>
>>>> Of course you couldn't update the source table that way, because only
>>>> your temporary table has the row number.
>>>
>>> Well, if I include a column that uniquely identifies the row in my final
>>> SELECT then I should be able to.
>>>
>>> Thanks for your input!
>>>
>>>> "Jonathan Wood" wrote in message
>>>> news:ec5Lbz6AJHA.2056-at-TK2MSFTNGP05.phx.gbl...
>>>>> Rick,
>>>>>
>>>>>>I know I'm not answering your question, but why do you want row
>>>>>>numbers? You should keep track of rows by using the primary key of the
>>>>>>table, not some arbitrary current row number.
>>>>>
>>>>> A primary key would represent row numbers of all items in the table,
>>>>> not just the ones matching the criteria in my query. Row numbers have
>>>>> many uses, such as for paging. For my current need though, I'm simply
>>>>> trying to duplicate some functionality of an existing system that
>>>>> shows week numbers for the data displayed--this is what the client is
>>>>> used to. A table identity would not provide this functionality. Also,
>>>>> my numbers must be sequential in the order specified by the query, not
>>>>> by some arbitrary identity based on the order rows were created.
>>>>>
>>>>>> Of course I recognize that I have no idea what your business problem
>>>>>> is, so I hope you don't take this as an unwarranted flame mail.
>>>>>
>>>>> I don't mind being asked a question; however, if you are knowledgeable
>>>>> about SQL and didn't think it worth answering the question as part of
>>>>> your reply, you now have me thinking that there is no simple answer to
>>>>> my question. And I don't understand why that is. I guess I can simply
>>>>> load the records into an array and then number them myself. I would've
>>>>> just guessed this was a very simple task.
>>>>>
>>>>> Thanks.
>>>>>
>>>>> Jonathan
>>>>>
>>>>
>>>
>>> --
>>> Jonathan Wood
>>> SoftCircuits Programming
>>> http://www.softcircuits.com
>>>
>>
>

"Rick Byham, (MSFT)" wrote in message
news:Ow2AjO5BJHA.4104-at-TK2MSFTNGP02.phx.gbl...
>.
> So the basic idea here is to separate the data storage and manipulation (a
> database responsibility) from the presentation (a client application
> responsibility).
>.

I use the combination of Dataphor and Sql Server. I rarely have client
problems and rarely have Database problems because they are NOT two separate
worlds. If I'm in Redmond would you mind if I dropped in and chatted

www.beyondsql.blogspot.com