CODEWITHSUNDEEP
mysqlsqlsql-server
Chanky Mallick
Chanky Mallick

Reputation: 587

SQL Distinct keyword not returning distinct values for Multiple Columns

If i select only 1 column with Distinct then it's working fine if i select multiple columns with 1 distinct it returns duplicate values ..

SELECT
    DISTINCT MS.SRC_TABLE_NAME AS SRC_TABLE_NAME,
    MS.SRC_SYSTEM_ENVIRONMENT_NAME AS SRC_SYSTEM_ENVIRONMENT_NAME,
    MS.SRC_SYSTEM_NAME AS SRC_SYSTEM_NAME
FROM
    MAPPING_SPECIFICATION MS,
    MAPPING_DETAILS MD

WHERE
    MS.MAP_ID = MD.MAP_ID AND
    MD.STATUS = 'Active'

Returns Duplicate rows

 SELECT
        DISTINCT MS.SRC_TABLE_NAME AS SRC_TABLE_NAME        
    FROM
        MAPPING_SPECIFICATION MS,
        MAPPING_DETAILS MD

    WHERE
        MS.MAP_ID = MD.MAP_ID AND
        MD.STATUS = 'Active'

Works Perfectly if select only Distinct Row .

Upvotes: 0

Views: 199

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172518

You can try like this to get DISTINCT with multiple columns:

select 
(SELECT group_concat(DISTINCT MS.SRC_TABLE_NAME) FROM MAPPING_SPECIFICATION MS INNER JOIN MAPPING_DETAILS MD ON MS.MAP_ID = MD.MAP_ID 
WHERE MD.STATUS = 'Active') as SRC_TABLE_NAME,
(SELECT group_concat(DISTINCT MS.SRC_SYSTEM_ENVIRONMENT_NAME) FROM MAPPING_SPECIFICATION MS INNER JOIN MAPPING_DETAILS MD ON MS.MAP_ID = MD.MAP_ID 
WHERE MD.STATUS = 'Active') as SRC_SYSTEM_ENVIRONMENT_NAME,
(SELECT group_concat(DISTINCT MS.SRC_SYSTEM_NAM) FROM MAPPING_SPECIFICATION MS INNER JOIN MAPPING_DETAILS MD ON MS.MAP_ID = MD.MAP_ID 
WHERE MD.STATUS = 'Active') as SRC_SYSTEM_NAME

Its in MYSQL.

In SQL Server you can use Group By like this:

SELECT MS.SRC_SYSTEM_NAM AS SRC_TABLE_NAME,
          MS.SRC_SYSTEM_ENVIRONMENT_NAME AS SRC_SYSTEM_ENVIRONMENT_NAME,
          MS.SRC_SYSTEM_NAME AS SRC_SYSTEM_NAME
   FROM MAPPING_SPECIFICATION MS 
        INNER JOIN MAPPING_DETAILS MD ON MS.MAP_ID = MD.MAP_ID 
   WHERE MD.STATUS = 'Active'
   GROUP BY MS.SRC_SYSTEM_NAM, MS.SRC_SYSTEM_ENVIRONMENT_NAME, MS.SRC_SYSTEM_NAME

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

DISTINCT does not guarantee you have SRC_TABLE_NAME unique when you use it with other columns. Your rows are DISTINCT and it works perfectly fine

CREATE TABLE #MyTable(col1 INT, col2 INT);

INSERT INTO #MyTable VALUES (1,1), (1,2), (2,1),(3,1);

SELECT DISTINCT col1, col2
FROM #MyTable;

LiveDemo

Use GROUP BY instead with aggregate function like MIN/MAX/GROUP_CONCAT:

SELECT
    MS.SRC_TABLE_NAME AS SRC_TABLE_NAME,
    MIN(MS.SRC_SYSTEM_ENVIRONMENT_NAME) AS SRC_SYSTEM_ENVIRONMENT_NAME,
    MIN(MS.SRC_SYSTEM_NAME) AS SRC_SYSTEM_NAME
FROM MAPPING_SPECIFICATION MS
JOIN MAPPING_DETAILS MD
  ON MS.MAP_ID = MD.MAP_ID AND
WHERE MD.STATUS = 'Active'
GROUP BY MS.SRC_TABLE_NAME;

Also try to avoid comma syntax join and use JOIN. Keep in mind that SQL Server and MySQL support different aggregate functions.

Upvotes: 1

Related Questions