LINE NUMBER IN SQL
I have a Query that Returns a Number of Rows it works fine it sorts it by the Primary Key but I really need to display the Line Number of the Row that is Displayed for example
My Query Returns
Primary Key 5 Comments skadjfkads
Primary Key 8 Comments kjsdafkjdsak
What i want it to display and Please not there is other data in the table this is a subset
Line Number 1 Primary Key 5 Comments kdjakf
Line Number 2 Primary Key 8 Comments kasdjfksa
I am trying to do this in Microsoft SQL
My Query Returns
Primary Key 5 Comments skadjfkads
Primary Key 8 Comments kjsdafkjdsak
What i want it to display and Please not there is other data in the table this is a subset
Line Number 1 Primary Key 5 Comments kdjakf
Line Number 2 Primary Key 8 Comments kasdjfksa
I am trying to do this in Microsoft SQL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
frankytee
brilliant solution matthews
frankytee,
Thank you for the compliment :) Subqueries like that are a trick I learned here at EE.
Regards,
Patrick
Thank you for the compliment :) Subqueries like that are a trick I learned here at EE.
Regards,
Patrick
for many records (for example > 3000) this query worked quicker then subqueries,
and in my solution you can make sort by another field (for Example by Comment) then PrimaryKey fiel2 ;)
declare @temp table (LineNumber int identity(1,1), PrimaryKey int, Comment varchar(2000))
insert into @temp (PrimaryKey, Comment)
select PrimaryKey, Comment from YourTable order by Comment
select LineNumber, PrimaryKey, Comment from @temp
and in my solution you can make sort by another field (for Example by Comment) then PrimaryKey fiel2 ;)
declare @temp table (LineNumber int identity(1,1), PrimaryKey int, Comment varchar(2000))
insert into @temp (PrimaryKey, Comment)
select PrimaryKey, Comment from YourTable order by Comment
select LineNumber, PrimaryKey, Comment from @temp