April 11, 2011 at 7:18 am
Hi,
I have to find the row number of a particular id .My query gives 100s of record out of which I need the row number of a particular Id.
Eg Select row number where id =' '
Is this possible in sql server 2005 ?
Please help.
Thanks,
April 11, 2011 at 7:27 am
Use the ROW_NUMBER function for this. HEre is a link to the BOL page.
http://msdn.microsoft.com/en-us/library/ms186734.aspx
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 11, 2011 at 7:56 am
Thanks for your reply.
I tried this.
I have a temp table which contains the data.
select ROW_NUMBER() OVER (ORDER BY id)as RowNumber,*
From #TempSearch
where id=@id
and seq=@
Order by Id, Seq
end
It doesnt work when I run the sp its says invalid object TempSearch .
Thanks
April 11, 2011 at 7:58 am
can you post the whole SP?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 11, 2011 at 8:01 am
I found the mistake it was the wrong table name ,but this is giving me the rownumber as 1 and showing 1 record .
How do I get the actual row number for this record as it is present in the temp table.
Thanks,
April 11, 2011 at 8:35 am
Pink123 (4/11/2011)
I found the mistake it was the wrong table name ,but this is giving me the rownumber as 1 and showing 1 record .How do I get the actual row number for this record as it is present in the temp table.
Thanks,
I assume by actual row number you mean the physical location of the row within the table.
That raises the question as to WHY you need this row number, for what purpose?
April 11, 2011 at 8:35 am
What do you mean by 'actual row number'? Tables have no order and there's no automatic row number that you can query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 9:01 am
Gail is correct. There are no "row numbers" in a table. You can add an ORDER BY when you query the table, and in that case you can get the rows returned in a certain order every time, however the row number is not linked to a row.
As an example. If I have this People table:
ID Name
-- -------
1 Steve
2 Gail
and I query:
select ID, name from people order by name
I get
ID Name
-- -------
2 Gail
1 Steve
I could add a row number
SELECT row_number() OVER (ORDER BY [name])
, [Name]
FROM dbo.People
and get this:
Name
-- -------
1 Gail
2 Steve
But "Gail" isn't linked to "1" as a row number. If I do this:
INSERT people SELECT 3, 'Bob'
SELECT row_number() OVER (ORDER BY [name])
, [Name]
FROM dbo.People
I now get this:
Name
-- -------
1 Bob
2 Gail
2 Steve
Now "Bob" is 1. You can get row numbers, but they are only linked to an ORDER BY and a specific result set. If the data changes, the row numbers may move. This is why you have a primary key.
April 11, 2011 at 10:20 am
Pink123 (4/11/2011)
I found the mistake it was the wrong table name ,but this is giving me the rownumber as 1 and showing 1 record .How do I get the actual row number for this record as it is present in the temp table.
Thanks,
Does the #temp table already have row numbers?
Can you show the query which creates the temp table?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 11, 2011 at 2:23 pm
Thanks for your reply.Your explanation is really helpful.
April 11, 2011 at 2:25 pm
No the temp table doesnt have any rownumbers.
It sjust
insert into #temptable
select ....
from ...
then I do a select rownumber as mentioned in my query and it gives rowcount as 1
as it will return 1 row always for a particular record.
Thanks,
April 11, 2011 at 2:35 pm
What are you trying to do here?
As mentioned, tables don't have a built-in row number, so please explain a bit clearer what it is that you are trying to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 11, 2011 at 2:38 pm
Pink123 (4/11/2011)
No the temp table doesnt have any rownumbers.It sjust
insert into #temptable
select ....
from ...
then I do a select rownumber as mentioned in my query and it gives rowcount as 1
as it will return 1 row always for a particular record.
Thanks,
What does "row number" represent in your data? the physical "location" of the row in the result set, a count of items before/after the current row or some other business rule?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 11, 2011 at 3:08 pm
Pink123 (4/11/2011)
No the temp table doesnt have any rownumbers.It sjust
insert into #temptable
select ....
from ...
then I do a select rownumber as mentioned in my query and it gives rowcount as 1
as it will return 1 row always for a particular record.
Thanks,
Change this to put a row number into the #temp table.
INSERT INTO #temptable
SELECT rn=ROW_NUMBER() OVER (ORDER BY...), ...
FROM...
ORDER BY whatever makes business sense.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply