SQL Server - Inner Join with Distinct

SQL Server - INNER JOIN WITH DISTINCT

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;

Get DISTINCT records on INNER JOIN SQL Server

  with cte as (SELECT ROW_NUMBER() over(PARTITION BY sdi.ID order by sdi.ID) as rn,totalCount = COUNT(*) OVER(), mailbox.ID as mailboxID, 
          sdi.ID as sdiID
           FROM [SDI].dbo.UserDocumentLocationOutbox mailbox 
           INNER JOIN [SDI].dbo.SDITransaction
           sdi on mailbox.SDITransactionID=sdi.ID 
         INNER JOIN [SYSDB].dbo.DocumentType doc on sdi.DocumentTypeID=doc.ID 
    where mailbox.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7' 
    and mailbox.UserProfileID = 'f9791614-8cc0-42e3-87d1-53709bc1e099' 
    and doc.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7' 
    and doc.Active=1 and doc.HideInMailbox=0  
    order by sdi.ProcessedDateTime desc 
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY)
select totalCount, mailboxID, sdiID
from cte 
where rn < 2

Using DISTINCT inner join in SQL

I did a test on MS SQL 2005 using the following tables: A 400K rows, B 26K rows and C 450 rows.

The estimated query plan indicated that the basic inner join would be 3 times slower than the nested sub-queries, however when actually running the query, the basic inner join was twice as fast as the nested queries, The basic inner join took 297ms on very minimal server hardware.

What database are you using, and what times are you seeing? I'm thinking if you are seeing poor performance then it is probably an index problem.

Distinct IDs from one table for inner join SQL

Your first and second query are similar.(just that you can not use ; inside your query) Both will produce the same result.

Even your second query which you think is giving you desired output, can not produce the output what you actually want.

Distinct works on the entire column list of the select clause.

In your case, if for the same a.id there is different a.test_group available then it will have multiple records with same a.id and different a.test_group.

DISTINCT from One table and INNER JOIN with another table in snowflake

If the goal is to use JOIN on T1 table only as a filter, IN/EXISTS could be used:

SELECT T2.*
FROM DB1."PUBLIC"."TABLE2" AS T2 
WHERE T2."Id" IN (SELECT T1."Id" FROM DB1."PUBLIC"."TABLE1" AS T1)
  AND T2."queryGroupName" NOT IN ('DELETE');

How to get DISTINCT row from INNER JOIN Query in SQL Server

The easiest way would be to join just one subject per teacher in the first place. So join with an aggregate:

select
  t.teacherid, 
  t.teachername, 
  g.gender, 
  t.dob, 
  coalesce(s.subject, 'no subject') as subject, 
  t.contact, 
  t.address, 
  t.email, 
  t.photo
from teacher t
join gender g on g.genderid = t.gender
left join 
(
   select teacherid, max(subjectid) as max_subjectid
   from teachersubject
   group by teacherid
) ts on ts.teacherid = t.teacherid
left join subject s on s.subjectid = ts.max_subjectid;

Inner join with distinct slow

Assuming you have the appropriate indices in place, using distinct is expensive. You should be able to get better performance using exists:

SELECT     
    cn.CountryID,
    cn.Code as CountryCode,
    cn.Name as CountryName
FROM dbo.Countries AS cn
WHERE EXISTS (
    SELECT 1
    FROM dbo.Provinces AS p
              INNER JOIN dbo.Cities c on c.ProvinceID = p.ProvinceID
              INNER JOIN dbo.Listings AS l ON l.CityID = c.CityID
    WHERE p.CountryID = cn.CountryID
        AND l.IsActive = 1 
        AND l.IsApproved = 1
)

SQL Query - Distinct on One Column for Distinct Value of Other (with INNER JOIN)

If you don't care which IP address you keep for each user_id / enum combo, then something like this should do:

SELECT user_id, min(client_ip_address), language_enum_code
FROM vw_user_session_log AS usl
INNER JOIN vw_user_topic_ownership AS uto
ON usl.user_id = uto.user_id
where client_ip_address is not null
group by user_id, language_enum_code

sql distinct join 3 tables

Probably you're looking for something like

SELECT DISTINCT s.subcategory_id, s.Title, c.date
  FROM (
    SELECT subcategory_id, max(date) as last_entry
      FROM content c
     INNER JOIN content_subcategories cs
        ON c.content_id = cs.content_id
     GROUP BY subcategory_id
  ) latest 
 INNER JOIN content_subcategories cs ON latest.subcategory_id = cs.subcategory_id 
 INNER JOIN content c ON c.content_id = cs.content_id AND c.date = latest.last_entry
 INNER JOIN subcategories s ON s.subcategory_id = latest.subcategory_id
ORDER BY c.date DESC
LIMIT 5;

Edit:
Corrected the limit to 5. Notice, that this is MySQL syntax (as you didn't provide any information about the DBMS you're using).

---

Related Topics