I have two tables
Table4 - Columns (ID, NAME, Money)
Table5 - COlumns(ID, PLACE)
Note: Table 4 has more IDs than table5
I am trying to use a join such that I get the following table
Table6 - Columns(Rank,ID,Place,Name,Money)
Also I want it to be such that when I get this result the ID values that are contained in Table4 whether or not they are contained in Table5 tp retain their ID and Name and Money Values.
In addition I would also like to have a rank column in the new table as the first column where the rank shown is 1 if your Money is the highest in the ID, and 2 if it is second highest etc.
So far I tried using the following join
Select * From Table4 left outer join Table5 on Table4.ID = Table5.ID
but the problem here is that the LID values unique to table4 drop their corresponding values for name and money.
Also how can I use the rank function here to get the desired result I tried using this but to no avail
select * from ( SELECT RANK() OVER (PARTITION BY ID ORDER BY Money DESC) as RN,ID,Place,Name,Money FROM Table4 )