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 (
   FROM Table4
  • You say "Table 4 has more IDs than table5" and also "I want .. the ID values that are contained in Table4 but not in Table5 retain their ID and Place." PLACE comes from table 5. If there is no row in 5 to match a row in 4, the result cannot possibly have a PLACE. Please re-word your question to make this clearer. Adding example data for tables 4, 5 & 6 would be a good start, with clear descriptions of how rows match to create the result, and what happens to non-matching rows. Sep 12 '18 at 11:10
  • 1
    The example RANK() partitions by a column called Subject. This is not mentioned in the table definitions. That query also has NAME & MONEY in Table5 which contradicts the earlier statement. Please edit the question to make it consistent. Sep 12 '18 at 11:15
  • I am sorry for the earlier typos, I was in a hurry to post this hence had a lot of them . I have improved them now and hopefully this makes it much more clearer
    – Noob101
    Sep 12 '18 at 11:35
  • 1
    Is this sql or sql-server. Please note, "sql" is a language, not a database management system. SQL Server (by MIcrosoft) is a database management system - it's important to properly tag your question so we can provide appropriate answers.
    – Hannah Vernon
    Sep 12 '18 at 14:22
  • 1
    Also, please create a minimal, complete, and verifiable example including T-SQL definitions of the tables involved, some sample data as INSERT INTO statements, and the desired output. As it stands, it's difficult to determine what you're really asking.
    – Hannah Vernon
    Sep 12 '18 at 14:23

With out some sample code to go on I created my own set

I had to join on both ID and Name for the rank in order to prevent cross joins

create table #Table4 (ID int, Names nvarchar(25), Moneys Money)
create table #Table5 (ID int, Place nvarchar(25))

insert into #Table4(ID,Names,Moneys)values(1,'waffles',1000), 
insert into #table5(ID,Place)values(1,'House'),(2,'Kitchen')

select x.Rn, t4.ID,t5.Place,t4.Names,t4.Moneys
from #Table4 t4
left join #table5 t5
    on t4.ID = t5.ID
inner join (select t4a.ID, t4a.Names, rank() over(partition by t4a.ID order 
     by t4a.Moneys desc) as Rn from #Table4 t4a) x
    on t4.ID = x.ID 
    and t4.Names = x.Names
order by ID,RN

Results will look something like this

Rn ID Place Names Moneys
1 1 House waffles 1000.00
1 2 Kitchen Pancakes 2000.00
1 3 NULL Toast 5000.00
2 3 NULL Eggs 4000.00
3 3 NULL Blueberries 3000.00

  • Is there a way to improve this by making sure that when the PLACE is NULL we get Ranks that are still arranged in a way such that Rank 1 of the PLace NULL is displayed the highest ?
    – Noob101
    Sep 12 '18 at 13:22
  • I don't follow your question. Are you trying to rank on place or on money? Using the sample above could you, if needed, add some more records and list how you would like to see the items?
    – saihtam8
    Sep 12 '18 at 13:29
  • I am trying to rank on money , just as you have done, however, I want the rank column to be such that given any ID the highest rank i.e.1 is displayed above rank 2 belonging to the same ID . I was thiking maybe adding a line such as ORDER BY RN would do it
    – Noob101
    Sep 12 '18 at 13:31
  • yup an order by rn would correct it and if you wanted to keep it together within the ID it would be order by ID,rn I'll Edit my post so it reflects that.
    – saihtam8
    Sep 12 '18 at 13:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.