CODEWITHSUNDEEP
sqlsql-server
jamheadart
jamheadart

Reputation: 5323

Am I looking for a cross join?

If I have two tables:

id_table     entry_table
--------     -----------
 [Id]         [entries]
  1             a
  2             b
  3             c
  4             d
  5

Is there a way to select the results into another table where I get:

select_results
--------------
[Id] [entries]
1     a
2     b
3     c

I'm trying e.g.

Select top 3 * from id_table, entry_table

and vice versa, but that gives:

1  a
1  b
1  c

These two tables SHOULD have the same amount of entries but I'm using "top 3" at the minute to see if it's possible.

Maybe I need a cross join with a where clause?

Upvotes: 1

Views: 53

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

Alternatively you can use row_number() window analytic function with top keyword as

select top 3   
       [Id], [entries]
  from id_table i
  join ( select row_number() over (order by [entries]) as row_id, 
                [entries]
           from entry_table ) e
    on i.[Id]=e.row_id;

Demo

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can use row_number() and then use join 

select a.*,b.* from
(select *,row_number() over(order by Id) rn1
from id_table
)  a  
join     
(select *,row_number() over(order by entries) rn1
from entry_table
) b on a.rn=b.rn1

Upvotes: 2

Related Questions