SQL Server - INNER JOIN WITH DISTINCT

Question

I am having a hard time doing the following   select a FirstName  a LastName  v District from AddTbl a order by Firstname inner join  select distinct LastName from             ValTbl v  where a LastName   v LastName      I want to do a join on ValTbl but only for distinct values

User · Accepted Answer

Try this   select distinct a FirstName  a LastName  v District from AddTbl a    inner join ValTbl v   on a LastName   v LastName order by a FirstName    Or this  it does the same  but the syntax is different    select distinct a FirstName  a LastName  v District from AddTbl a  ValTbl v where a LastName   v LastName order by a FirstName

User · Answer

I think you actually provided a good start for the correct answer right in your question  you just need the correct syntax   I had this exact same problem  and putting DISTINCT in a sub-query was indeed less costly than what other answers here have proposed  select a FirstName  a LastName  v District from AddTbl a  inner join  select distinct LastName  District      from ValTbl  v    on a LastName   v LastName order by Firstname

User · Answer

You can use CTE to get the distinct values of the second table  and then join that with the first table   You also need to get the distinct values based on LastName column  You do this with a Row Number   partitioned by the LastName  and sorted by the FirstName   Here s the code   WITH SecondTableWithDistinctLastName AS           SELECT            FROM                          SELECT                                 ROW NUMBER   OVER  PARTITION BY LastName ORDER BY FirstName  AS  Rank                      FROM    AddTbl                              AS      tableWithRank         WHERE   tableWithRank  Rank    1    SELECT          a FirstName  a LastName  S District FROM            SecondTableWithDistinctLastName AS S INNER JOIN      AddTbl AS a     ON          a LastName   S LastName ORDER   BY      a FirstName

User · Answer

It s not the same doing a select distinct at the beginning because you are wasting all the calculated rows from the result   select a FirstName  a LastName  v District from AddTbl a order by Firstname natural join  select distinct LastName from             ValTbl v  where a LastName   v LastName    try that

User · Answer

add  distinct  after  select    select distinct a FirstName  a LastName  v District   v LastName from AddTbl a  inner join ValTbl v  where a LastName   v LastName  order by Firstname

User · Answer

select distinct a FirstName  a LastName  v District from AddTbl a    inner join ValTbl v   on a LastName   v LastName order by a FirstName   hope this helps

[sql] SQL Server - INNER JOIN WITH DISTINCT

Examples related to sql

Examples related to inner-join