Stupid question - SQL Server Forums

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)

Stupid question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-03 : 08:06:03

SQLNewbie writes "I have 2 tables.

Table 1 is like this:
Col1
A
B
C

Table 2 is like this:
Col2
1
2
3

How do I write a query to get a result like this?
Col1 Col2
A 1
B 2
C 3"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-03 : 08:19:53

select a.Col1, b.Col2
from (select Col1, seq = (select count(*) from tbl1 t1 where t1.Col1 <= t.Col1) from tbl1 t) a
join (select Col2, seq = (select count(*) from tbl2 t1 where t1.Col2 <= t.Col2) from tbl2 t) b
on a.seq = b.seq

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-11-03 : 21:25:49

For this question, you don't need the second table

.
Select t1.Col1, (select count(*) from yourTable t2 where t2.Col1<=t1.Col1) as Col2
from yourTable t1

Hippi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-04 : 00:54:15

quote:
Originally posted by Hippi

For this question, you don't need the second table .
Select t1.Col1, (select count(*) from yourTable t2 where t2.Col1<=t1.Col1) as Col2
from yourTable t1

Hippi

If table2 has values like

2
3
4

then yours wont work

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources