There is a request:

SELECT addressCode FROM action

Displays route points for example:
one
2
3

I need one more column for the result to be like this:
1 | 3
2 | 2
3 | 1

How to do it? inner join the same field? I already thought of making a temporary table and in the temporary table updating the last column with an inverted result. advise how to do?

  • You can, if you number the lines, and append "according to numbering", but ... it will be ordered, and if not ordered, the request will be even more difficult. - nick_n_a
  • @nick_n_a, if you number and attach according to numbering, what do you mean by row_number and a temporary table? I still do not understand what key to connect. Ideally, this is a SELECT addressCode, addressCode1 FROM action here join order by addressCode asc, addressCode1 desc, if it turned the columns independently of each other - Winteriscoming
  • Swap rats can not be. One cursor returns uniquely one line. It is possible through the temporary. It seems to me that what you do is better implemented in a high level language :) - nick_n_a
  • @nick_n_a this is for rdl report - Winteriscoming

1 answer 1

It is possible, an example with nested queries, I think it would be better to rewrite it to a temporary table, but this is also possible.

 select * from ( select row_number() over (order by (select null)) rn ,addressCode from action -- этот кусок можно вынести в временную таблицу ) t1 left join ( select row_number() over (order by (select null)) rn ,addressCode from action -- этот кусок можно вынести в временную таблицу ) t2 on t1.rn = (select count(*) from action) - t2.rn + 1 -- и сюда в к-во тоже подставить временную

If an ordered list fits you, it is better this way, but then without a temporary table:

 select * from ( select row_number() over (order by addressCode asc) rn ,addressCode from action ) t1 left join ( select row_number() over (order by addressCode desc) rn ,addressCode from action ) t2 on t1.rn = t2.rn
  • one
    why left join? - 4per
  • You can left without. - nick_n_a
  • ok. Any code design style is a matter of taste. But you have it somehow not sustained at all. In particular: the first subquery has no indents, and the second has. Before the first subquery, the bracket is not transferred to a new line, but before the second one is transferred. Comments on my screen did not fit into the frame of the snippet. I could make a better edit, but I’m afraid to impose a particular style. - 4per
  • one
    although no. not ok All the same, the request should reflect what we want to receive. The fact that with the left join above query works the same way as with the inner join no reason to use it, IMHO. When reading, you have to wonder why we expect to get everything from the first subquery, and why something might not be in the second. - 4per
  • I tried on the working base, to my surprise, it worked as with null in the addressCode field, but the trick is that rn will never be null (there will always be an integer with the line number), therefore both inner and left will return the same result. And the type of join, and I really used to always indicate. I think that left should work faster, since it does not check whether there is a value in both tables (there is no check for additional filtering of the sample) - nick_n_a