How to populate 1st row of existing column into new column of 2nd row?

Question

I have a query that generate following output:

| columnA |
-----------
|       5 |
-----------
|       2 |
-----------
|       3 |
-----------
|       4 |
-----------

Is there a solution that I could generate another column which is exactly the same as columnA (lets put a name for it - columnB), and having value from columnA that moving downward. Below is the expecting output:

| columnA | columnB |
---------------------
|       5 |       0 | -> put zero for 1st row
---------------------
|       2 |       5 | -> original value from 1st row of columnA
---------------------
|       3 |       2 | -> original value from 2nd row of columnA
---------------------
|       4 |       3 | -> original value from 3rd row of columnA
---------------------

That's about my question.

sql being unordered I presume you've got an id in there somewhere? how do you know what order the rows are in? — dice, Mar 09 '12 at 10:13
the query already have a filter applied on it, thus if first row is five. Sorry for my bad description of the question. — huahsin68, Mar 09 '12 at 10:18
Can you post your query? You would need to perform your query a second time, but with the reverse order. But how to join those two queries together??? — Wim, Mar 09 '12 at 10:43
@huahsin68: Is this Oracle? Also, dice's question was about ordering, not filtering - there is no inherent ordering in SQL, so without a column to order by, references to the 1st/2nd/3rd/nth row are meaningless. — , Mar 09 '12 at 11:23
@MarkBannister I am doing it in Informix, and there is no ordering. — huahsin68, Mar 09 '12 at 12:47
@Huahsin68: Then how do you tell which is the "previous" row? As I said before, **there is no inherent ordering in SQL, so without a column to order by, references to the 1st/2nd/3rd/nth row are meaningless**. — , Mar 09 '12 at 12:53
I am assuming the data is sorted in some previous step, but the column it was ordered by is dropped along the way. I am not sure whether this is a good practice / safe to rely on, but the ordering is kept at least in Oracle. For example, the result of select A from (select * from T order by B) will remain ordered by B. — svinja, Mar 09 '12 at 12:58
@MarkBannister Noted. I'll amend accordingly. If the query has order by, how should I reference to 1st/2nd/3rd/nth row? — huahsin68, Mar 09 '12 at 13:10
Could you maybe give us the original query huahsin68? Or an explanation of what you're trying to do conceptually - what the relation between the rows is; why the first row is first, the second row second, etc. — svinja, Mar 09 '12 at 13:12
@huahsin: In informix that appears to be quite difficult - I haven't used it myself, but it appears to lack the windowing functions available in many dialects of SQL. One way would be using a correlated sub-query, though this is likely to perform poorly; another method can be found in the answers to this SO question: http://stackoverflow.com/questions/119278/row-numbers-for-a-query-in-informix . (Although I see that Informix does have a rowid function on unfragmented tables.) — , Mar 09 '12 at 13:17

svinja · Answer 1 · 2012-03-09T11:19:13.560

IN PL/SQL:

-- this gets just the first line
select A A1, null A2 from
    (select A from TABLE)
where rownum = 1
union all
-- this gets the rest of the lines
select Q1.A A1, Q2.A A2 from
    (select A, rownum RN from (select A from TABLE)) Q1    
    join
    (select A, rownum RN from (select A from TABLE)) Q2
    on Q1.RN = Q2.RN + 1

(select A from TABLE) is the inner query that provides the original list. Tested and does what you want. Probably should somehow alias the query that appears multiple times but I don't know how to do that off the top of my head.

You could also replace

(select A, rownum RN from (select A from TABLE))

with

(select A, rownum RN from TABLE))

if you don't mind modifying the original query.

score 1 · Answer 2 · answered Mar 09 '12 at 11:41

In Transact SQL:

       WITH main AS (SELECT ROW_NUMBER() OVER (ORDER BY ColumnA ASC) as rownum, 
                             ColumnA 
                        FROM MainQuery)

     SELECT ISNULL(parent.ColumnA,0) as ColumnA,  
            ISNULL(child.ColumnA,0) as ColumnB 
       FROM main parent FULL OUTER JOIN main child
         ON parent.rownum = child.rownum + 1

Substitute "MainQuery" for the query generating the original columnA.

This produces zeros where the two columns don't overlap (i.e. first and last rows). As mentioned by dice and Mark Bannister, the row positions are meaningless without some kind of ordering. This is captured by the

ROW_NUMBER() OVER (ORDER BY ColumnA ASC)

Which needs to change to how you want the data to be ordered.

How to populate 1st row of existing column into new column of 2nd row?

2 Answers2