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
– Chanky Mallick, Commented Nov 13, 2015 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
– Gordon Linoff, Commented Nov 13, 2015 at 12:09

Lukasz Szozda · Accepted Answer · 2015-11-13 12:18:31Z

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, 2015 at 12:18

answered Nov 13, 2015 at 12:14

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CM2K Over a year ago

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

Lukasz Szozda Over a year ago

@CM2K You probably want to write about stackoverflow.com/questions/33629168/…. MySQL allows column without grouping, SQL Server not. Good point.

CM2K Over a year ago

exactly, those kind of answers are very useful

Rahul Tripathi · Accepted Answer · 2015-11-13 12:19:10Z

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

Collectives™ on Stack Overflow

SQL Distinct keyword not returning distinct values for Multiple Columns

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related