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: 2622
  • Last Modified:

SQL to return a record counter per row?

I'd like to have a query that returns the number of the record as a column in the record set, e.g. something like
SELECT <count of current Row> , * from <My Table> where <whatever>

so that the results are

1    <column>  <column>   etc
2    <column>  <column>   etc
3    <column>  <column>   etc
4    <column>  <column>   etc

Note that the counter I need is for the returned set, not the identity col or equivalent RowID of the underlying table.
As I recall there was some trick to do this by creating a table of integers, but I forget what it is.
0
JohnBPrice
Asked:
JohnBPrice
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi JohnBPrice,

SELECT Rank = (SELECT Count(*) From urTable b WHERE a.PK >= b.PK),
Column1 ,Column2
FROM urTable a


Cheers!
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT Rank = (SELECT Count(*) From urTable b WHERE a.PK > b.PK)+1,
Column1 ,Column2
FROM urTable a
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the second one is the corrected one ..
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Scott PletcherSenior DBACommented:
There's no easy way to do that in SQL 2K.  You can use:
(1) an intermediate temp table with an IDENTITY column <or>
(2) do an ordered list with a subquery to determine the relative row number (note: this can be an expensive subquery).


--(1)
SELECT IDENTITY(INT, 1, 1) AS [Row#], ...
INTO #report
FROM ...
WHERE ...

SELECT *
FROM #report


--(2)
SELECT (SELECT COUNT(*) FROM tableName tn2 WHERE tn.[key] >= tn2.[key]) AS [Row#], ...
FROM tableName tn
WHERE ...
ORDER BY [key]
0
 
JohnBPriceAuthor Commented:
Works great, but a couple changes are needed, the where clause must be included in both places, and the order by must be the primary key or the rank gets out of sequence.

SELECT Rank = (SELECT Count(*) From urTable b WHERE <YourWhereClause> a.PK > b.PK)+1,
Column1 ,Column2
FROM urTable a
WHERE <YourWhereClause>
ORDER BY a.PK
0
 
JewelersITCommented:
I realize this is a dead topic, but I've found a much better solution that does not involve an expensive subquery. You can use the ROW_NUMBER() function.

For example:

SELECT ROW_NUMBER() OVER(ORDER BY PK) as RowNumber, PK, Column1, Column2 FROM urTable
0
 
JohnBPriceAuthor Commented:
@JewelersIT
Nice, but requires SQL Server 2005.  Not all my servers are there yet.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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