Create Counter Row

[ddfff]

Is there a way in SQL query to create a column in the query results that is the row number?

 

[cLFlaVA]

What is your definition of "row number"? The order of the data in a table is irrelevant until you ORDER it in a query.

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[SQLSister]

Will this give you an idea?

Select  proposalnumber,  identity (int, 1, 1) as col2  into #test
from proposals where leadestimator = 'butt, Jamie'
select * from #test

Questions about posting. See faq183-874

 

[voyageur]

I'm looking for something similar but because I'm running the query from MS SQL 2000 Report Server I can't use the INTO clause. I need the equivalent of the Oracle rownum virtual field. Any suggestions?

 

[SQLSister]

CAn't you run a stored procedure from Report server? Put the temp table into the sp. YOu can deinfe the temp table explicitely with the identiy field and then doa regular insert itno it as well. There is no rownum virtual field.

Questions about posting. See faq183-874

 

[voyageur]

Great suggestion, thanks

 

[ESquared]

SELECT
   RowNum,
   Y1.*
   FROM
      YourTable Y1
   INNER JOIN (
      SELECT
         T1.UniqueOrKeyColumn,
         RowNum=Count(*)
      FROM
         YourTable T1
         INNER JOIN YourTable T2 ON T1.UniqueOrKeyColumn <= T2.UniqueOrKeyColumn
      GROUP BY
         T1.UniqueOrKeyColumn
   ) Y2 ON Y1.UniqueOrKeyColumn = Y2.UniqueOrKeyColumn

This of course would require modification to handle WHERE clauses...

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

So e[sup]2[/sup], did you find the solution to the Rank problem in the depths of your genius or somewhere else? Although, I'd hate to see what that derived table would do to the server when there are 100,000 rows in the underlying table. What's the summation formula again, n*(n+1)/2?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I originally saw this method on a web site. I can't claim it as my own.

Anyway, Karl, SQLSister's method with the identity column is best in terms of performance for large results. You are right on that score. But I have no clue what the summation formula is. It's possible it's as bad as n[sup]2[/sup] but something tells me it's not because the second table only has count(*) and doesn't name a column. Actually, count(1) might work faster.



-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[donutman]

It took me a while but I finally remembered how to derive it:
[tt]
z = 1 + 2 + 3 + 4... + n
z = n + n-1 + n-2 + n-3 + 1
+_________________________________
2z = n+1 + n+1 + n+1 + n+1...+ n+1
[/tt]
so
2z=n(n+1) and
z=n(n+1)/2

Correct me if I'm wrong SQLSister, but any attempt at inserting a sorted rowset is doomed to failure on large rowsets. Of course the initial post didn't insist on a particular order, so your solution is valid for unpredictable orders. Hmm would it make a difference if an identity column is a clustered index and you don't use an Order By clause?
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

AFAIK another Karl made this formula - Gauss, when he was 5 years old.

Btw. does unstable (order of) insertion depend on parallelism in exec plan?

 

[yksvaan]

Exec plan gurus, a question from us less experienced:
If I try that idea with pubs (seen this on another site)
SELECT e.emp_id, e.lname, e.fname, e.job_id,
(SELECT COUNT(*) FROM employee e2 where e2.emp_id <= e.emp_id ) as rownumber
FROM employee e
ORDER BY emp_id
The exec plan shows nested loop to the PK index seek. Of course the number of executes will be high, but shouldn't it not consume space. And I know when there's more data, exec plan could be different.
Would you consider this efficient? Is it probable to run out of server resources with lets say 200000 rows?

Cheers

 

[donutman]

Geeze, I don't even understand the questions from vongrunt and yksvaan, so don't look for any answers from me. I'm only capable of plagiarizing the works of children.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

yksvaan... that correlated subquery could run with exactly the same execution plan of my query. But then again, it might not. It could run that inner query once for each row, and that would be a gigantic bummer (yes, I'm using technical terms where appropriate).

Logically, our queries have an identical result. I'd be interested to see how they stack up against each other in the real world.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)