sql server programming: Hi all,
I am looking for a way where we can use Sequence Table to generate row
number for non unique rows .
(Sequence is a table of numbers from 1 to (n); this is a common SQL
programming trick. )

With warm regards
Jatinder

Thanks David,

In case of Authors Table it is easy to count number of rows having id
less than a particular row that is perfectly OK.
But what if the query is complex and I wish to number them .
One method would be again as suggested by you but using a view [
instead of query becuase the query is complex]

If the changes are very less for example in entire list of columns only
one coulmn is changing then we have to find a unique id like this
(columna+coumnb+columnc....)<=(columna+coumnb+columnc....)
I am not opposing this approach but looking for a simple approach

Or other method (if possible ) to use sequence table.

Roji. P. Thomas Suggested using IDENTITY which is again perfectly OK
select identity(int,1,1) myid,* into newTable from BaseTable
then
select * from NewTable

With warm regards
Jatinder

What does "non unique rows" mean? Why would you want to store non unique
rows?

Perhaps you just mean you want to number results in a query. You don't need
a sequence table to do that. For example:

SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS row_num,
*
FROM Authors AS A

Better still, do it client side.

--
David Portas
SQL Server MVP
--

I think the simplest approach is client side. The example code you posted
adds the numbers in an arbitrary order. Your narrative also suggests you
aren't too concerned about the sequence ("we have to find a unique id"). In
other words your requirement has nothing to do with the data, it's
presumably just for presentation purposes. Presentation belongs in the
client / middle tier.

--
David Portas
SQL Server MVP
--

Why dont you use the IDENTITY feature instead?


--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com


[quoted text, click to view]