Calculate a row number column in the results of a Select statement ? 1

[sb001]

I want to union several select statements together to form a single result set that them ordered with an even distribution of the rows from each of the individual selects.

i.e

If first SELECT returns...
COL1 ROWNUM
---- ------
'ABC' 1
'DEF' 2
'GHI' 3

and a second SELECT returns...
COL1 ROWNUM
---- ------
'RST' 1
'UVW' 2
'XYZ' 3

the UNION results ordered by ROWNUM would be...
COL1 ROWNUM
---- ------
'ABC' 1
'RST' 1
'DEF' 2
'UVW' 2
'GHI' 3
'XYZ' 3

The question is how can I produce ROWNUM on the fly as part of the SELECT statement or is their another way (do I have to use stored procedures ?)

Help!!

 

[christine1]

Can you give me the example query you're using? I think I know what you want to do I just want to be sure.

all you may have to do is write it like this:

Select distinct...

Or

Select count(*)...

Let me know what the example query looks like.

 

[sb001]

I don't believe I will be able to achieve it through distinct or count(*) so I may have explained it ambigously...

I want the results from select 1...

somecol
-------
'Apples'
'Pears'
'Bannas'

Merged with the results from select 2...

somecol
-------
'grapes'
'lemons'
'kimi fruits'

but the order of the merged results has to be one row from the first select followed by one row of the second select and so on, to get...

somecol
-------
'Apples'
'grapes'
'Pears'
'lemons'
'Bannas'
'kiwi fruits'

I was hoping to achieve it by including some calculated counter in the result of each individual select given me the row number of that row. I could then order by that column in the union of these selects to achieve to intermingled result set. My problem is how to generate this calculated counter (similar to ROWNUM in ORACLE) in SQL Server.

My belief currently is that it can't be done and I will have to resort to a stored procedure to build up the required result set (in which case is there an equivalent to an ORACLE PL/SQL table in SQL Server - basically a memory based table or array).

All help appreciated...

Steve

 

[calahans]

Hi,

It seems that you problem would be better solved by returning two record sets and use a programming language to do the sort.

 

[ColinM]

Heres something that might be useful, but probably not quite what you're looking for.
I use this technique of creating a table with an indentity and then inserting into it quite often.

CREATE TABLE Tbl1
(ID int IDENTITY(1,1) NOT NULL,Data varchar(255) NOT NULL)
CREATE TABLE Tbl2
(ID int IDENTITY(1,1) NOT NULL,Data varchar(255) NOT NULL)

insert into Tbl1
SELECT SomeCol FROM YourTable1
insert into Tbl2
SELECT SomeCol FROM YourTable2

SELECT Data FROM Tbl1,Tbl2
WHERE Tbl1.ID = Tbl2.ID
ORDER BY Tbl1.ID

 

[MalcolmW]

A slight variation on that will get you the right sort and number of rows,
select Data
from(
SELECT Data, ID, 0 as sort
FROM #Tbl1
UNION
SELECT Data, ID, 1 as sort
FROM #Tbl2
) as derivedtable
order by ID, sort