Row numbers in select statements

Is there an easy way to do get a row number in each row in a select
statement?
Something like:
select rownumber,description price from lineorder order by row number
Thanks,
TomTshad,
Yes.
See:
How to dynamically number rows in a SELECT statement.
http://support.microsoft.com/defaul...kb;en-us;186133
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||Why can't the presentation tier do this? It's the only place that HAS to
loop through each row, one by one. Now you're forcing the database to do it
too, and performance can only suffer for it.
http://www.aspfaq.com/2427
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eV$hgGZ2FHA.636@.TK2MSFTNGP10.phx.gbl...
> Tshad,
> Yes.
> See:
> How to dynamically number rows in a SELECT statement.
> http://support.microsoft.com/defaul...kb;en-us;186133
I tried that:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2
then r.ItemName end,Price,PurchaseQty,Price * PurchaseQty
order by 1
But in my 8 rows returned I got (1,1,1,1,1,1,2,2)'
Is the Joins causing me a problem?
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OUJZsHZ2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Why can't the presentation tier do this? It's the only place that HAS to
> loop through each row, one by one. Now you're forcing the database to do
> it too, and performance can only suffer for it.
Actually, it can.
But that is an extra step,as I am binding it to a datagrid.
Tom
> http://www.aspfaq.com/2427
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
My philosophy is that rules of database normalization apply only to physical
tables and not to query results.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||> My philosophy is that rules of database normalization apply only to
> physical tables and not to query results.
FWIW, my objection to doing this in the database has nothing to do with
normalization at all, but with the extra work required (whether using a
subquery, or copying all the data to a separate table first). A simple
counter at the presentation layer is the very least impact on performance,
since it has to loop through all rows and display them one by one anyway.
A|||This assumes that the result is being consumed in such a way that the
developer can add the additional computed column. It may be exported from
DTS to a table or file, executed only in Query Analyzer and pasted into
email, or bound to a datagrid.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utST6ik2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> FWIW, my objection to doing this in the database has nothing to do with
> normalization at all, but with the extra work required (whether using a
> subquery, or copying all the data to a separate table first). A simple
> counter at the presentation layer is the very least impact on performance,
> since it has to loop through all rows and display them one by one anyway.
> A
>|||> This assumes that the result is being consumed in such a way that the
> developer can add the additional computed column. It may be exported from
> DTS to a table or file, executed only in Query Analyzer and pasted into
> email, or bound to a datagrid.
Yep, that's why I asked, "why can't the presentation tier do this?" and did
not say "ONLY the presentation tier can do this!"|||But do you think that only the presentation tier *should* do this?
;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhisP5k2FHA.1572@.TK2MSFTNGP10.phx.gbl...
> Yep, that's why I asked, "why can't the presentation tier do this?" and
> did not say "ONLY the presentation tier can do this!"
>