Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2310
  • Last Modified:

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
0
DRSLT
Asked:
DRSLT
  • 2
1 Solution
 
Patrick MatthewsCommented:
SELECT
    (SELECT COUNT(t2.*) FROM YourTable t2 WHERE t2.pk <= t1.pk) AS LineNumber,
    t1.pk,
    t1.col1,
    t1.col2,
    t1.col3,
FROM YourTable t1
ORDER BY t1.pk
0
 
frankyteeCommented:
brilliant solution matthews
0
 
Patrick MatthewsCommented:
frankytee,

Thank you for the compliment :)  Subqueries like that are a trick I learned here at EE.

Regards,

Patrick
0
 
sas13Commented:
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
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now