SQLTeam.com | Weblogs | Forums

Select same column twice with different query and join


how do i query column 'a' twice with results being different and have the results show as two new columns in a table for eg. col a = 'robert' 'david' 'john', col b = '1' '2' '3' and colc = '6' '9''4' then i select cola and order desc by b on my first query and select cola and order by c on my next query. How do i display the result as a table with two columns of a cola only.
query1 query2
john david
david robert
robert john
ive tried putting results into two seperate temp table but cant get it to order. Ive been googleing but dont know how to phrase the question.
Thanks in advance Robert


Try this:

with cte
  as (select col_a
            ,row_number() over(order by col_b desc) as rn_b
            ,row_number() over(order by col_c desc) as rn_c
        from yourtable
select a.col_a as query1
      ,b.col_a as query2
  from cte as a
       inner join cte as b
               on b.rn_c=a.rn_b
 order by a.rn_b


Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.
This is insane!

You do not query a column; you query tables. A column is a scalar value, not a set!

A query returns a set of rows. I am going to guess that your mindset is sooooo screwed up that you think a table is a sequential tape file that it will read the next record. But rows are nothing like records.

then I select cola and “ORDER DESC BY b DESC” on my first query and select cola and ORDER BY C on my next query. <<

A table has no concept of ordering. NONE! Nada! Neniam!

How do I display the result as a table with two columns of a cola only. <<

SQL is based on a tiered architecture. The database tier handles all of the database retrieval and data integrity. But nothing else. The data display and formatting is done in presentation layers that get data from the database layer.


Thank-you for your replys. Thanks bitsmed, that works perfect for me. jcelko, thank-you for your points, I am a layman to sql and I guess my mindset is screwed up. I will read up on each of the points you make. On a lighter note, I now know what my daughter must feel when she comes home with her school work and I point out her errors.
Regards Robert