How to get the Row number for the current row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the Row number for the current row

Author

Topic

jane0829
Starting Member

6 Posts

Posted - 2013-11-25 : 13:29:00

Hi ALL

I am trying to get the current row number using this:
SELECT ROW_NUMBER() OVER (ORDER BY QUANTITY) FROM RDR1 WHERE Row = CurrentRow

However i got error message says row is an invalid column name

Please advise.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 13:31:52

this is enough


SELECT ROW_NUMBER() OVER (ORDER BY QUANTITY)AS Rno,* FROM RDR1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jane0829
Starting Member

6 Posts

Posted - 2013-11-25 : 13:37:16

Thank you visakh16
Will this command give me the current row number?
For example, if i am current on line 2, will it give me "2" as the result of this query?
And if possible, could you please explain "* from RDR1"?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 13:40:50

quote:
Originally posted by jane0829

Thank you visakh16
Will this command give me the current row number?
For example, if i am current on line 2, will it give me "2" as the result of this query?
And if possible, could you please explain "* from RDR1"?
Thanks

yep... it will based on your order (in this case by Quantity)
* means select all other columns
from RDR1 means thats the table from which you want to fetch the data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jane0829
Starting Member

6 Posts

Posted - 2013-11-25 : 13:47:24

Instead of a list of all the rows and columns
i will just need a row number
For example, if i selected a field at line 4, and i run this query, it will give me a value of 4
And if i selected a field at Line 10, it will give me a value of 10 as the query result.

quote:
Originally posted by visakh16

quote:
Originally posted by jane0829

Thank you visakh16
Will this command give me the current row number?
For example, if i am current on line 2, will it give me "2" as the result of this query?
And if possible, could you please explain "* from RDR1"?
Thanks

yep... it will based on your order (in this case by Quantity)
* means select all other columns
from RDR1 means thats the table from which you want to fetch the data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-25 : 14:16:12

if i selected a field at line 4, and i run this query, it will give me a value of 4
And if i selected a field at Line 10, it will give me a value of 10 as the query result.
what do you mean by Lane 4,Lane 10 etc? there's no concept of Lane or order in sql table unless you specify it by means of a order clause

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

jane0829
Starting Member

6 Posts

Posted - 2013-11-25 : 14:35:24

I apologize for any confusion.
I am currently working on SAP Business One and it is based on SQL DB
In my case, i have this Sales Order with 10 line items.
In my query, i would like it to reflect the row number that i am currently on.
For example, my field is $[$38.0.0.1]
1 here means row 1
instead of changing the row number each time when i switch to another row.
I would like to use a query to identify the current row number automatically.

quote:
Originally posted by visakh16

if i selected a field at line 4, and i run this query, it will give me a value of 4
And if i selected a field at Line 10, it will give me a value of 10 as the query result.
what do you mean by Lane 4,Lane 10 etc? there's no concept of Lane or order in sql table unless you specify it by means of a order clause

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-25 : 14:45:46

SQL deals with sets (generally), so there is no such thing a current row for a set. So you have some sample data you can provide that illustrates your issue and what you want the results to be?

jane0829
Starting Member

6 Posts

Posted - 2013-11-25 : 14:50:21

For example

# Item Quantity Price
1 A1 100 $1
2 A2 200 $2

These two are the line items in my Sales Order
If i am currently highlighting the quantity 200 in row 2
I want to have a query, which will give me a result of 2, reflecting the row that i am currently on.

quote:
Originally posted by Lamprey

SQL deals with sets (generally), so there is no such thing a current row for a set. So you have some sample data you can provide that illustrates your issue and what you want the results to be?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-25 : 14:55:00

I'm still not understanding what you want. How is SQL Server supposed to know what row you have highlighted?

jane0829
Starting Member

6 Posts

Posted - 2013-11-25 : 15:00:05

Maybe i was wrong in the first place
Please let me figure it out first before further questions
Thank you so much

quote:
Originally posted by Lamprey

I'm still not understanding what you want. How is SQL Server supposed to know what row you have highlighted?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-25 : 15:03:26

quote:
If i am currently highlighting the quantity 200 in row 2

this statement implies you are viewing this data in a front end application. The database can not know which row you have highlighted in the application. If you want to return this row from the underlying database then you need to pass in a row identifier. If people can write to this table while you are viewing it then a row_number() function may change between requests. Usually, because of that, the application will/should have a permanent row identifier along with this logical row. That would be the values of a primary key or unique constraint. Then your query would pass in that/those values to get that specific row.

However, if the data is not likely to change between requests then you could say:


declare @currentRow int
set @currentRow = 2 --passed in value

select <RDR1Columns>, @currentRow
from   (
       SELECT <RDR1Columns>, [row] = ROW_NUMBER() OVER (ORDER BY QUANTITY) 
       FROM RDR1 
       ) d
WHERE d.[Row] = @CurrentRow

But it would be better to pass in the permanent primary key value(s) (or other unique constraint value(s))

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources