Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.
Thanks in advance for all.Is it not doable?
Originally posted by RaedT
Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.
Thanks in advance for all.|||The short answer is NO. TSQL operations are set based, and no ordering is guaranted by the server unless the developer or programmer specifies it, thus row numbering is pointless because the same query run on two different occasions could result in the same record being assigned different row numbers.
The long answer is YES, if your result set is sorted by a unique key or combination of columns, then you can write a Select statement that loops back on itself and counts the number of records less than each record. This is an expensive query to run and can be difficult to debug, so my recommendation to you would be to use a temporary table (actually, a table variable is more efficient) as long as it suits your needs.
Why do you need the results numbered? While there are some circumstances where this is beneficial, it is often a sign of problems with the database schema or the application design concept.
blindman|||If you don't have already a client, I may consider to put your SELECT statement in the software of a (ADO) client, where you can make use of the AbsolutePosition property of a recordset.
Showing posts with label retrieving. Show all posts
Showing posts with label retrieving. Show all posts
Monday, March 19, 2012
Displaying the row number in a query
Sunday, February 19, 2012
display record by retrieving parameter value in module
in my report, i have user name, test name, start time, end time, full marks and grade. when user click on "report this test" after finishing a certain test, i want the report displays this record which contains current user, test and result details. my sql query is:
SELECT CYMUSER.UNAME, TEST.TNAME, RESULT.STARTTIME, RESULT.ENDTIME, RESULT.TOTALMARKS, RESULT.GRADE
FROM CYMUSER INNER JOIN
TEST ON CYMUSER.UID = TEST.UID INNER JOIN
RESULT ON CYMUSER.UID = RESULT.UID AND TEST.TID = RESULT.TID
WHERE (RESULT.RID = @.RID)
the @.RID parameter is held in my moduleUser.curRID.
How can i pass the @.RID value to report and let it display the only one record that i want?I created a sqldataadapter and a dataset, set the select command as the query mentioned above, and get the dataset from CrystalReportCurResult.rpt
it seems working. However, when i open the form that contains the report viewer at run time, it asks for server and database name, user name and password for the database. In fact my database is local and no username and password at all.
can anyone help with the problem plz? being waiting for reply for past 5 hrs.|||if i get the field data from the database straight way, it wont ask for anything, but will display all the data in the requried column. If i get field data from Project Data --> ADO.NET Datasets, no matter which dataset i use, it asks for database login details (i.e. server, db, name and pw) at runtime. annoying, help plz!|||You need to use Windows Authentication mode of SQL Server
SELECT CYMUSER.UNAME, TEST.TNAME, RESULT.STARTTIME, RESULT.ENDTIME, RESULT.TOTALMARKS, RESULT.GRADE
FROM CYMUSER INNER JOIN
TEST ON CYMUSER.UID = TEST.UID INNER JOIN
RESULT ON CYMUSER.UID = RESULT.UID AND TEST.TID = RESULT.TID
WHERE (RESULT.RID = @.RID)
the @.RID parameter is held in my moduleUser.curRID.
How can i pass the @.RID value to report and let it display the only one record that i want?I created a sqldataadapter and a dataset, set the select command as the query mentioned above, and get the dataset from CrystalReportCurResult.rpt
it seems working. However, when i open the form that contains the report viewer at run time, it asks for server and database name, user name and password for the database. In fact my database is local and no username and password at all.
can anyone help with the problem plz? being waiting for reply for past 5 hrs.|||if i get the field data from the database straight way, it wont ask for anything, but will display all the data in the requried column. If i get field data from Project Data --> ADO.NET Datasets, no matter which dataset i use, it asks for database login details (i.e. server, db, name and pw) at runtime. annoying, help plz!|||You need to use Windows Authentication mode of SQL Server
Subscribe to:
Posts (Atom)