Monday, March 19, 2012
Displaying the row number in a query
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.
Sunday, February 19, 2012
Display only the latest two consecutive years?
Hi all,
I have a Matrix with several static rows of financial information with a column group that groups the information by date:
01.02.04 12.09.03
Dividend 455,789 346,098
Profit 100,787 264,687
Deposits 89,078 76,003
Currently I have a filter on the column group that limits the information to the top two dates (Fields!dateField.Value Top N =2). What I also need is to filer the data so that only consecutive years are displayed. I have tried the filter (Year(Fields!dateField.Value) >= Year(Max(Fields!dateField.Value)) - 1), but this doesn't stop non-consecutive years being added to the Matrix. What am I missing here?
Any help you have to offer is much appreciated!
Thank you,
Stephen.
stephen,
i think that this task seems to be "database work" - just evoke the needed data by an appropriate sql-statement.
cheers
markus
Thank you for your reply.
Is there any way that I can do this without altering the underlying dataset? Unfortunately, the query already returns all of the data that is required and nothing more, so limiting any of the data there would cause problems elsewhere in the report.
Stephen.
|||How about creating a second dataset with a modified query to get only the data you need. So then you have one dataset with all the dataand one with the last two years.|||That's a good idea! I will have a play around with it and will let you know how I get on.Thanks!
Regards,
Stephen.|||
That seems to have done the trick!
Thanks!
Stephen.