Using a SELECT within the column list of a select statement

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)

Using a SELECT within the column list of a select statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-17 : 09:58:54

Craig writes "I want a select statement that returns columns from a table and an additional column that contains a value which is incremented by 1 for each row. For example e.g.

declare @num int
set @num = 1

SELECT Column1, Column2, (SELECT @num = @num+1) AS RowNumber
FROM Table1

Obviously the SQL above doesn't work but its similar to something I've seem before where you can have a column thats value is different per row returned because it is acting upon values in other columns.

Thanks"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-17 : 10:03:27

use pubs
select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-19 : 04:48:52

Alternatively this one


use pubs
select rank=(Select count(*) from authors where au_lname+Au_Fname<=T.Au_lname+T.Au_Fname) , au_lname, au_fname
from authors T order by Au_lname

Madhivanan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources