How to insert Sequence column to Views

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to insert Sequence column to Views

Author

Topic

ham_tim_hieu
Starting Member

4 Posts

Posted - 2007-02-24 : 11:26:28

I have a Table, example Table1
Col1 Col2
a aa
b bb
c cc
Now I want to creat a Table Views, that have content the same as Table1 includes Sequence column, example View1
Seq Col1 Col2
1 a aa
2 b bb
3 c cc

How should I do, thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-24 : 19:02:57

[code]
select Seq = (select count(*) from table x where x.col1 <= t.col1)
col1,
col2
from table t
[/code]

ham_tim_hieu
Starting Member

4 Posts

Posted - 2007-02-24 : 23:20:48

Dear KH, I understand your idea, thanks
Table1 that I show in that question is just only for example. But in fact, Table1 is not a real Table, it is a result of very complex query: SELECT ... FROM .. WHERE ... GROUP BY ... HAVING ... ORDER BY....., if I do as your idea
select Seq = (select count(*) from (SELECT ... FROM .. WHERE ... GROUP BY ... HAVING ... ORDER BY.....) as x where x.col1 <= t.col1)
col1,
col2
from (SELECT ... FROM .. WHERE ... GROUP BY ... HAVING ... ORDER BY.....) as t

So I have to run this complex query twice, it may take slowly, not optimized
Should you have another better way?
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-25 : 02:31:47

"Should you have another better way?"
Yes. Don't do it in SQL. Do it in your front end application where you present the data

ham_tim_hieu
Starting Member

4 Posts

Posted - 2007-02-25 : 03:56:23

I have an idea, I use Stored Procedures instead of Table View
In my SP, I creat a temporary table, and I insert query result to this table, then I execute your sql with temporary table
It is optimized? (I prefer do in Database than in Application)
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-25 : 04:44:31

The best option is still to do it in Application. It will be only one single select compare to an insert & a select (insert into temp table and then select from the temp table) if you do it in the stored procedure.

Depending on the number of rows return and the complication of the query, the Stored Procedure way might not be efficient.

"I prefer do in Database than in Application"
Even if doing it in Application is much easier and faster than doing it in Database ?

If you absolutely had to do it in database, create the temp table with the seq as identity column.

ham_tim_hieu
Starting Member

4 Posts

Posted - 2007-02-25 : 06:58:05

OK, I use "create the temp table with the seq as identity column" because even my query is complex but it didn't return many rows in result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources