SQL Distinct keyword not returning distinct values for Multiple Columns

Question

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 .

@SubinCPoonamgode MS.SRC_TABLE_NAME .. that i need only unique — Chanky Mallick, Nov 13 '15 at 12:04
What database are you using? Please tag your question appropriately. Also, clarify what you want to return. `DISTINCT` is working fine. You just don't understand what it does. — Gordon Linoff, Nov 13 '15 at 12:09

Lukasz Szozda · Answer 1 · 2015-11-13T12:18:31.500

1

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.

edited Nov 13 '15 at 12:18

answered Nov 13 '15 at 12:14

Lukasz Szozda

162,964
23
234
275

1

sorry, I rushed with the question. Saw multiple selects and and groupped by one single field. Then i saw those multiple selects were aggregate functions. So it can be sql server – CM2K Nov 13 '15 at 12:17
@CM2K You probably want to write about http://stackoverflow.com/questions/33629168/group-by-clause-in-mysql-and-postgresql-why-the-error-in-postgresql/33629201#33629201. MySQL allows column without grouping, SQL Server not. Good point. – Lukasz Szozda Nov 13 '15 at 12:20
exactly, those kind of answers are very useful – CM2K Nov 13 '15 at 12:22

score 0 · Answer 2 · answered Nov 13 '15 at 12:19

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

SQL Distinct keyword not returning distinct values for Multiple Columns

2 Answers2