RowNumber using SQL Query

Hi All,
I have following table structure,

----------------------
ChallanID ProductID PublicationDate Description Qty Amt
----------------------
43 9 4/1/2006 ABC 1 880
43 10 5/1/2006 BCA 1 930
43 11 5/1/2006 CBA 1 230

I want a sql query which select all the record with a serial number eg:

---------------------
SN# ChallanID ProductID PublicationDate Description Qty Amt
----------------------
1 43 9 4/1/2006 ABC 1 880
2 43 10 5/1/2006 BCA 1 930
3 43 11 5/1/2006 CBA 1 230use a temp table with identity column and insert your result into the temp table and select it back.|||U havent mentioned about how records to be ordered? In what order u want to generate serial No:?|||temp table shemp table...

SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName

ps. I got this example from somewhere and it is not original work. If the original author sees this and takes any offense I am will to erase from the forum.|||SELECT count(*) as [SS#],a.LastName
FROM Employees a join
Employees b
on a.LastName >= b.LastName
group by a.LastName
order by a.LastName
No, that only works if you use a unique key i.e.
select id=1,name='ccc' into #t1 union all
select 3,'bbb' union all
select 4,'aaa' union all
select 9,'bbb'

select count(*) as [ss#], name=min(a.name)
from #t1 a, #t1 b
where a.id>=b.id
group by a.id
order by 1
else use a temp table with identity column as suggested by khtan
select ss#=identity(int,1,1),name into #t2 from #t1 order by name
select * from #t2|||Problem is, either way you have no guarantee that the "serial number" for any record won't change as the contents of the table changes. Seems to me a "serial number" is expected to be static, so you really should add it as a permanent column to your table (perhaps as an identity datatype).

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

You could use dynamic sql (execute a string using EXEC or sp_executesql).|||Thanks

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

There are several options:

1. Use dynamic SQL to generate the entire SELECT statement

2. Use CASE expression in the ORDER BY clause like:

ORDER BY case @.SortColumn when 1 then col1 end,

case @.SortColumn when 2 then col2 end

3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.

There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.

|||Thank you so much

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!"
>