Menu
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.
862
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.
DISTINCT is for all the columns in the select clause, which means all columns combined will be used to get distinct rows. TOP X will only select the number of rows mentioned in X .
Different Types of SQL JOINs Here are the different types of the JOINs in SQL: (INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table.
The SQL SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
