Help - with a tricky select, pls

[dim]

Hi, I'm tring to break my head finding a solution of how to return the following result:

I have the source table -> S_TAB with some data like

COL1 COL2 COL3

ABC DTT COL
ANC DRT COL
ANC DRT COL
......
......

what I need is come up with a single select statement (a view) to get the following output:

select <something> as RID, COL1, COL2, COL3 from S_TAB

RID COL1 COL2 COL3
1 ABC DTT COL
2 ANC DRT COL
3 ANC DRT COL
4 .....
5 .....


Any Idea will be appreciated
Dim

[Claire]

From my knowledge,this cant be done by a single select statement.Always you need some other steps to achieve this.

[skhanal]

You can create a temporary table and run select * from it


select IDENTITY(int, 1,1) AS rowid, col1, col2, col3
into #mytable
from s_tab


select * from #mytable

You can wrap this in a stored procedure.


Or if you really want go to do it in a view, you can do something like this (it is purely academic)

--modify it for your table, where condition needs a primary key column

select a.au_id, a.au_lname, a.au_fname, count(*) as 'rowid'
from authors a, authors b
where a.au_id >= b.au_id
group by a.au_id, a.au_lname, a.au_fname


It will be highly inefficient for any large table.