CODEWITHSUNDEEP
sql
Josh Diehl
Josh Diehl

Reputation: 3079

Joining two tables together strictly by order

If I have two tables(t1, t2), each with one column

t1      
letters  
a         
b         
c         

t2
nums
1
2
3

Is it possible to "join" the two together in a way that produces a two-column result set that looks like this:

letters nums
a       1
b       2
c       3

Requirements for the solution:

Bonus points: - If two tables have different row counts, final result set is count of the max of the two tables, and the "missing" data is nulls.

Just wondering if this is possible given the constraints.

Upvotes: 4

Views: 134

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want to use row_number(). However, SQL tables represent unordered sets, so you need a column that specifies the ordering.

The idea is:

select l.letter, n.number
from (select l.*, row_number() over (order by ?) as seqnum
      from letters l
     ) l join
     (select n.*, row_number() over (order by ?) as seqnum
      from numbers n
     ) n
     on l.seqnum = n.seqnum;

The ? is for the column that specifies the ordering.

If you want all rows in both tables, use full join rather than an inner join.

EDTI:

row_number() is the obvious solution, but you can do this with a correlated subquery assuming the values are unique:

select l.letter, n.number
from (select l.*, 
             (select count(*) from letters l2 where l2.letter <= l.letter) as seqnum
      from letters l
     ) l join
     (select n.*, 
             (select count(*) from numbers n2 where n2.num <= n.num) as seqnum
      from numbers n
     ) n
     on l.seqnum = n.seqnum;

I find the restriction on not using row_number() to be rather absurd, given that it is an ISO/ANSI standard function supported by almost all databases.

Upvotes: 6

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

If your version of SQL supports an ASCII function (which can generate an ASCII code for each lowercase letter), then you may join on the ASCII code shifted downwards by 96:

SELECT
    t1.letters,
    t2.nums
FROM table1 t1
INNER JOIN table2 t2
    ON t2.nums = ASCII(t1.letters) - 96;

enter image description here

Demo

Upvotes: 2

Related Questions