-
Help - with a tricky select, pls
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
-
From my knowledge,this cant be done by a single select statement.Always you need some other steps to achieve this.
-
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.
Last edited by skhanal; 05-13-2003 at 03:45 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|