Difficult Data Manipulation

[mcbigj]

Hello,
I need to do a seeminly difficult result query from a sequentially numbered table. I'll provide an example but here's what needs to be done.
I need to split a table in half and then reorder them 1 from each half, incrementing until completely rebuilt. Here's the scenario:

Say I have a recordset of 100 records. The first half would be 1 to 50 and second half 51-100. I would need to create the following recordset
1
51
2
52
3
53
etc....

I'm sure I could do this in a very inefficient way by creating temp. multiple recordsets then manipulating into a resulting set. But I wonder does SQL have any functionality to where this could be done in a single query/ storedprocedure?

All ideas Welcome.
Thanks

 

[JonathanC]

I wouldn't say temp tables are inefficient.
Using cursors when you can substitute it would temptables
will far be more inefficient.

 

[tlbroadbent]

You can use the Case statement in the Order By clause. Perhaps, a query like the following will do what you want. Adjust the values in the Case statement as needed.

Select RecNo
From TblName
Order By
Case When RecNo>50
Then RecNo-50 Else RecNo End,
RecNo
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[mcbigj]

heh, well that was easy Thank you so much, it works great.