How to create a row id for all records across a union all query 2

[eo]

Hi,

SQL 2005 - My view does a UNION ALL. I have used the...

Row_Number() over (order by alias.field)

...logic to create a row id, but it does not give me a continious row id across the two union all sub queries.
I realise I could use the...

newid()

...option, but this delivers a long arbitrary code, and as the users are actually going to use this row id field in reports, such a long, albeit unique field, will be no good.

Any idea how I can obtain a simple row counter id to work in a continious manner across two union subqueries?



EO
Hertfordshire, England

 

[bborissov]

How about:

DECLARE @Test TABLE (Row int IDENTITY(1,1), Fld1 int, Fld2 varchar(200))
INSERT INTO @Test (Fld1, Fld2)
SELECT Ordinal_Position, Column_Name
FROM master.INFORMATION_SCHEMA.Columns WHERE Table_Name = 'spt_fallback_db'
UNION ALL
SELECT Ordinal_Position, Column_Name
FROM master.INFORMATION_SCHEMA.Columns WHERE Table_Name = 'spt_monitor'

SELECT * FROM @Test

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[eo]

I was just in the process of createing a sp with temp tables, and then to query that temp table with the...

Row_Number() over (order by alias.field)

...logic over the temp table. This gives me a continious row id, (similar to your suggestion).

Thanks!




EO
Hertfordshire, England

 

[PDreyer]

select row_number () over (order by number),*
from (
select * from master..spt_values where type='P' and number<10
union all
select * from master..spt_values where type='P' and number<10
)union_query

 

[eo]

could you expand a bit? if I can get away with NOT using declared temp tables, then great!!

Lets assume the following union all query:

select A.PrimaryKey, A.Field1, A.Field2, '' as Field3
from tableA A

union all

select B.PrimaryKey, '', B.Field2, B.Field3
from tableB B

I am not sure how to apply your logic to such a union query as to include the required continious row id

EO
Hertfordshire, England

 

[bborissov]

Add your query as derived table:

SELECT ROW_NUMBER() OVER (ORDER BY ?????????)
FROM ( /* here goes your query */
select A.PrimaryKey, A.Field1, A.Field2, '' as Field3
from tableA A

union all

select B.PrimaryKey, '', B.Field2, B.Field3
from tableB B) Tbl1

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Hey!
That was NOT my suggestion. I just explained how PDreyer's code works. He deserves the star, no me

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[eo]

Brilliant!! I tried this, and it makes my final SP 441 lines long as opposed to over 1000!!
A star given to PDryer

EO
Hertfordshire, England