How do I get the row number?

I have tryed to find a way to include the row number in a querry but without result.

I have a table select some rows ordered and now I will add a column called rank
How do I get the rownumber in the field rank ?
Thanks in advance
Jan O

Jan O
www.olsmar.com
0
jan_olsmar
3/2/2005 1:41:03 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
661 Views

Similar Articles

[PageSpeed] 58

The way I'd suggest assigning a row number to your resultset is to INSERT your resultset into a temporary table or table variable which has an IDENTITY column,  something like this:


-- set up a table variable to hold the resultset
DECLARE @myTable (Rank int identity(1,1) primary_key,
ColumnA varchar(20),
ColumnB varchar(20)
)
-- insert the resultset into the table variable
INSERT INTO
@myTable
(
ColumnA,
ColumnB
)
SELECT
ColumnA,
ColumnB
FROM
someTable
ORDER BY
someCriteria
-- return the resultset to the calling program in rank order, including the rank column
SELECT
Rank,
ColumnA,
ColumnB
FROM
@myTable
ORDER BY
Rank

Terri
Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
3/2/2005 1:58:23 PM
Thanks,

Have hoped not to do it so, a lot of other calculation dependences.

Jan

Jan O
www.olsmar.com
0
jan_olsmar
3/2/2005 2:08:41 PM
Well, an alternative (which performs poorly) would be at the very bottom of this link: Returning a Row Number in a Query It involves using a subquery for every row in your resultset and could be horrendous performance-wise.  

Can you just assign the rank in the front end?
Terri

Terri Morton
Engagement Manager, Neudesic

How to ask a question

0
tmorton
3/2/2005 2:58:47 PM
I agree with Terri. If possible, assign in the front end. I have used the temp table solution before, as well. It really depends on why you need to have the rownumber. If it is just for display purposes, it should be no problem to create on the front end when binding your resultset.


-pjm

I never gave a damn about the meterman until I was the man who had to read the meter.
0
pjmcb
3/2/2005 3:18:21 PM
Thanks again. 

I have started to solve the "problem" in the front end.
I have a datagrid with paging so I have a few lines left.
Got it working whitout paging , but I had prefered asystem rownr from SQL.
But you cant get everything for free :)
Thanks
Jan


Jan O
www.olsmar.com
0
jan_olsmar
3/2/2005 3:21:02 PM
Reply:

Similar Artilces:

How to set the row number and get the row number for ("selected row) in Datagrid
I face a problem that i can't get the selected row of the datagrid in both server side script (VB.net) and client side script (javascript) As i can't use the button column / hyperlink column in the datagrid. Thus , i need to handle the change the background color of the selected row and also to get the selected row number for Update/Delete. Thanks in advance. This depends on how do you perform the selection. Do you want to implement chaning of the SelectedRow at the client side without any postback? If you have no issues with postback, then you can do s little trick here: 1. ...

get row number in a sql
Hi, In ASE 12.0 version,how can I get a sequal row number in a single sql? If the sql is: select DeptID,DeptName from Departments I hope the result set is: sequal no dept id dept name 1 101 Finance 2 102 Human 3 103 IT 4 104 Sale Regards, Michael Zhao How about: select identity(32) as "sequal_no", DeptID, DeptName into #foobar from Departments select * from #foobar order by sequal_no drop table #foobar Michael Zhao wrote: > > Hi, > In ASE 12.0 version,how can I get a sequal row numb...

How can I get row number in sql select?
Hi, all. How can I get row numbers in select statement? For example, I have a sql like: select emp_id, emp_name, emp_salary from employee order by emp_id Now, I want to add row numbers in the result set. I know we can add a getrow() expression in datawindow, but I do want to know how to do this in select statement. I'm using sybase 11.0.2, pb6. Best regards. Feng This works in Sybase SQL Anywhere, I'm not sure whether it'll work in Sybase 11.0.2 select number(*), emp_id, emp_name, emp_salary from employee order by emp_id Feng Zhu ...

How to get Row Numbers from SQL Express 2005 query?
 Hi,  I'm using SQL Express 2005 and VIsual Studio 2005, and this sounds like it should be easy. I'm trying to return the row numbers of my queries, but if I use the Row_Number() command I get the following error: "The OVER SQL construct or statement is not supported." So, is Row_Number() not supported in SQL Express 2005? If not, how can I return row numbers with my queries? Or, more specifically, how can I return a limited result set from a query (i.e. Return only row number 10-20)? My current command is as follows:  SELECT (SELECT R...

Get the Row Number or Row ID
Hi, I am using Sybase Central v4.3 with Interactive SQL to query the SQL Commands. My concern is how to get the rownumber or row id in select queries. Is there any in built commands in the ASE. If not please suggest me how to get the rownumber. Thanks.... Unless i'm mistaken, ASE does not use a rowid... it can have a timestamp, but it is updated with every modification to the row. http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.blocks/html/blocks/blocks34.htm -- Cory Sane [TeamSybase] Certified Sybase Associate DBA for ASE 15.0 "Santhanam P" wrote...

The row number and offset of each row in the page should have a matching entry in row number table
Is the proper correction for the following to recreate the table BCPout/IN or table to table insert, or just drop and recreate the index? Row number is 0. Is there a way to correct just the data page? Table Corrupt: The row number and offset of each row in the page should have a matching entry in row number table; check this page (page#=772121 row#=0 offset in row number table=0) Table Corrupt: The row number and offset of each row in the page should have a matching entry in row number table; check this page (page#=772121 row#=0 offset in row number table=0) Table Corrupt: The row...

How do you get the number of rows
I have a gridview and I want to display the total number of rows of data like 5000 rows? This is not the same as the GridView1.Rows.Count, which gives the number of rows being displayed like 10 of 5000 rows. How do I get at the total? Hi, i think what you are asking is how to get the number of rows of the bounded dataset. Lets say you bound a dataset to the grid, you only need to get the number of rows of the first table in the dataset: myDataset.tables(0).rows.count As simple as that!   regards, njs That works in ASP.NET 1.x That doesn't work in ASP.NET 2.0...

get number from row
if i have a this string to select a row from database of acess: select TOP 1 * from odkazy how i elicit from it id column? i need know id from select row then just get the id... select top 1 id from odkazy... PS..you would need an order by clause in the select statement otherwise the TOP has no meaning.. hth***********************Dinakar NethiLife is short. Enjoy it.*********************** my problem: (for example) if i have 5 columns with numbers in select row and i want display cast of this numbers I write: one = ds.tables("Odkazy").Rows(i)("...

SQL row number
Hi, I would like to know if it is possible with perl, when we search for a general 'thing'( i.e search in the database all people that speaks english) in a MySQL database, what is the row number of the last row that matches the SELECT statment? If so can someone show me an example? I would appreciate Anthony > Hi, Howdy, > I would like to know if it is possible with perl, when we search for a = general 'thing'( i.e search in the database all=20 > people that speaks english) in a MySQL database, what is the row = number of the last row that mat...

getting the number of rows...
how can you get the number of rows in query resault with out filling dataset and checking the "count"? afaik - you can't - - you've got to have an entity to count the rows - the dataset is the 'finished product' that contains the result of your query - until you've queried and received the data from the database, you don't know how many rows you've receivedDavid WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(Please 'Mark as ...

Getting the number of rows in a datagrid
Hi, The datagrid on my webform gets filled with some data from my database. I want to find the number of rows that are populated on the datagrid. What is the way to simply find the number of rows that have been filled with data. I want to use that in a for loop. Thanks, Snigdha After you populate your datagrid with data, all you have to do is:DataGrid.Items.CountThat will give you a count of how many rows are in your grid.DarmarkMark as Answer, if this reply answers your post. Get the row count based on your DataSource....For example, if the DaatSource is DataTable dtTable...it would ...

How to get a row number in DDDW
Hi, All, When a DDDW on a column opens for the user to pick an item, and when the user clicks on a row or scrolls up/down in the DDDW, is there any way to get the row number of the DDDW? I have tried this in PB 5, and it did not seem to work. Any ideas about PB 6.5 or PB 7.0? Thanks in advance. Ben You can't directly get the row that the user clicked on. You can write code in the ItemChanged event of the parent DW where you get the data value on the parent column, get a handle to the child DW, then perform a Find ( ) on the child DW looking the the data value in the rel...

Getting the number of Rows (Records)
How do I get and return the number of rows (records) in a sql 2005 database, Some code samples would be helpful Thanks Joe   Hi, that would be something like this: select count(*) from yourTable The * can also be replaced by a columnname but since I didn't know your table structure I used * in the example. Grz, Kris.Read my blog. Handy Firefox plugins for web developers.Workaround for non working Mark as answer buttons. Ok, guys this isn't going to work. Take a look at my code. I need it to return the current record that was inserted. Is there a was to do that &n...

Get Row Number in Datagrid
Hi I want to delete a databound row in a datagrid using the delete button on the datagrid. I have tried to use Dim RowID As Integer = Convert.ToInt32(shOTQDataGrid1.Items.Item(CInt(e.Item.ItemIndex))) to get the row number. Dim SoqID As Label = CType(shOTQDataGrid1.Items(RowID).FindControl("SOQID"), Label) I need to get the row number(e.g. 0). I search for the specific item "SOQID" and then use a custom Class to delete the record from the Database with the value of SoqID.Text. Dim sh As New ShiftOvertimeQualifiersClass Dim SoqID As Label = CType(shO...

Web resources about - How do I get the row number? - asp.net.sql-datasource

Number - Wikipedia, the free encyclopedia
A number is a mathematical object used to count , label, and measure . In mathematics , the definition of number has been extended over the years ...

Number - Wikipedia, the free encyclopedia
A number is a mathematical object used to count , label, and measure . In mathematics , the definition of number has been extended over the years ...

Yes, It’s the Donald Trump Election, and Here Are the Numbers to Prove it
As 2015 draws to a close, Andrew Tyndall has crunched the numbers on how much coverage the three evening newscasts are giving to the 2016 campaigns. ...

Join digits for big points in Sequence – Connecting Numbers
Sequence - Connecting Numbers 13 Thieves Threes! Two Dots The Mesh Join digits for big points in Sequence – Connecting Numbers is a story ...

Second Strong Payroll Number +211,000; December Rate Hike Assured
... Birth Death Model Starting January 2014, I dropped the Birth/Death Model charts from this report. For those who follow the numbers, I retain ...

Energy layoffs, by the numbers
After years of strong employment gains, the energy sector has become the biggest job cutter of 2015.

Apple is already making the next Apple TV in small numbers
... sets, but already Apple is hard at work on its successor, according to a new report. Apple’s suppliers are said to be putting a limited number ...

Report Shows Number Of Foreign Fighters Joining ISIS Has DOUBLED In A Year
'The motivation for people to join violent extremist groups in Syria and Iraq remains more personal than political'

Rising tide? Number of officers charged in shootings tripled this year to 15
... affirms some strategies for police accountability and details how far those strategies have to go, the Baltimore Sun reports that the number ...

‘Galavant’ Season 2 Opening Number Riffs On Renewal, Emmy Snub & Scheduling
Dan Fogelman is keeping the self-deprecating tone of his medieval musical comedy series Galavant intact in a big-scale opening number penned ...

Resources last updated: 12/9/2015 1:23:50 AM