SQL Table Pivot-like Requirement

[marklaw01]

I want to use SQL to re-arrange the records in a table. In the first table below, the records are arranged in numerical step order. We want them re-arranged in next step order.

If we start with the first table below, the query should generate the second table below. It does not matter if we need to add other columns to the output/query table, such as a first column that creates a new numerical order such as row numbers. A sequence of queries is also O.K.

Step# NextStep#
1 10
5 7
7 1004
10 52
52 5
1004

The re-arranged table should be

sequence
1
10
52
5
7
1004

Thanks in advance.

 

[expostfacto]

tables have no inherent ordering in ANSI SQL. so talking of "rearranging the records" makes no sense except in the context of clustering, which is database-specific.

there is also no ANSI way to generate the ordering you want. You will have to use a vendor extension like Oracle's CONNECT BY, or write your own stored procedure.

http://www.carnageblender.com:

Carnage Blender. Over 40 million battles served.

 

[marklaw01]

I have modified the requirement to include a row number column at the beginning of each table.

I want to use SQL to re-arrange the records in a table. In the first table below, the records are arranged in numerical step order. We want them re-arranged in next step order.

If we start with the first table below, the query should generate the second table below. It does not matter if we need to add other columns to the output/query table, such as a first column that creates a new numerical order such as row numbers. A sequence of queries is also O.K.

Row# Step# NextStep#
1 1 10
2 5 7
3 7 1004
4 10 52
5 52 5
6 1004

The re-arranged table should be

Row# sequence
1 1
2 10
3 52
4 5
5 7
6 1004

Thanks in advance.

 

[swampBoogie]

Contrary to what expostfacto claims,ANSI SQL do have support for this type of queries. The problem is that is only implemented by a few wendors, Oracle (9i) and DB2 are those that I am aware of.

Otherwise it is possible to write a recursive stored procedure to handle it.

 

[expostfacto]

what is the ANSI equivalent of CONNECT BY, then?

http://www.carnageblender.com:

Carnage Blender. Over 40 million battles served.

 

[swampBoogie]

You can use a with clause in the select statement

WITH queryName as (query) [,...] <query expression>

see page 265 ff of the ANSI SQL 99 specification for more details.

It is more general than CONNECT BY

 

[expostfacto]

thanks, good to know.

http://www.carnageblender.com:

Carnage Blender. Over 40 million battles served.