View Problem: serial number in the output

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 2005 Forums

Transact-SQL (2005)

View Problem: serial number in the output

Author

Topic

abhwhiz
Starting Member

37 Posts

Posted - 2007-03-01 : 07:14:17

Friends,

What i need is numbering the rows in the output of a select statement on the view. What happens here is after the columns from the first table it restarts the numbering. Is there any way to do this?

create view dbo.test22_vw
as
select ROW_NUMBER() OVER(ORDER BY id DESC) AS 'Row Number',
AttributeInstanceID,
MarketingCopy,
TitleTag,
KeywordTag,
DescriptionTag,
H1Tag,
H2Tag,
HotelResultTitleTag,
HotelResultDescPrefixTag,
HotelResultImageAltTag,
Status,
LocaleID,
FIleName
from HCOM_SEO_PAGE_AUTO_TEXT where pagetypeid=2
union all

select ROW_NUMBER() OVER(ORDER BY id DESC) AS 'Row Number',
InstanceID as AttributeInstanceID,
MarketingCopy,
TitleTag,
Keywords as KeywordTag,
Description as DescriptionTag,
'' as H1Tag,
'' as H2Tag,
'' as HotelResultTitleTag,
'' as HotelResultDescPrefixTag,
'' as HotelResultImageAltTag,
Status,
LocaleId,
FileName
from HCOM_SEO_OPTIMIZE_PAGE_TEXT where pagetypeid=2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-01 : 07:21:40

remove the ROW_NUMBER from the 2 query and do it in the derived table


create view dbo.test22_vw
as
select  ROW_NUMBER() OVER(ORDER BY id DESC) AS 'Row Number',
        . . . 
FROM
(
        select  . . .
        from    table

union all

        select  . . .
        from    table
) v

abhwhiz
Starting Member

37 Posts

Posted - 2007-03-01 : 22:02:51

Thnx KH, that worked like magic!! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources