CODEWITHSUNDEEP
sql-server
user4895544
user4895544

Reputation: 103

How to return distinct values from multiple joins on tables

I am trying to get distinct values from Message table but couldn't succeed though due to multiple joins on tables.

SELECT
    UR.CHATID,
    --U.USERNAME,
    MAX(UM.DATETIME)
FROM
    USER_REQUEST UR
JOIN 
    USER_MESSAGE UM ON UR.CHATID = UM.CHATID
JOIN 
    EXTENSION_USER EU ON EU.ID = UM.CHATUSERID
JOIN 
    USER_EXTENSION UE ON UE.CHATID = UM.CHATID
JOIN 
    User U ON U.ID = EU.USERID
WHERE
    CR.[CreatedOn] >= '2019-06-4 08:30:00'
    AND CR.CREATEDON < '2019-06-04 18:00:00' 
GROUP BY
    CR.CHATID

This query will fetch distinct records. Since I need UserName too on including UserName I won't get the required distinct results as in USER_MESSAGE table I have Duplicate CHATIDs.

The above query returns me the following result:

enter image description here

On including UserName I get the following result Where Chat Ids are duplicated:

[![enter image description here][2]][2]

Upvotes: 0

Views: 415

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

Perhaps ROW_NUMBER could be helpful here. This answer assumes that you want the record with the largest DATETIME value for each chat group.

WITH cte AS (
    SELECT UR.CHATID, U.USERNAME, UM.DATETIME,
        ROW_NUMBER() OVER (PARTITION BY UR.CHATID ORDER BY UM.DATETIME DESC) rn
    FROM USER_REQUEST UR
    INNER JOIN USER_MESSAGE UM
        ON UR.CHATID = UM.CHATID
    INNER JOIN EXTENSION_USER EU
        ON EU.ID = UM.CHATUSERID
    INNER JOIN USER_EXTENSION UE
        ON UE.CHATID = UM.CHATID
    INNER JOIN User U
        ON U.ID = EU.USERID
    WHERE
        CR.CREATEDON >= '2019-06-04 08:30:00' AND
        CR.CREATEDON < '2019-06-04 18:00:00'
)

SELECT
    CHATID,
    USERNAME,
    DATETIME
FROM cte
WHERE rn = 1;

Upvotes: 3

Related Questions