Select distinct returns duplicates

Question

I have the following query:

SELECT 
DISTINCT (TK.TICKETID),
TK.DESCRIPTION,
TK.CREATIONDATE,
TK.REPORTEDBY,
TK.OWNER,
WF.ASSIGNCODE
FROM ticket TK
INNER JOIN wfassignment WF on WF.OWNERID = TK.TICKETUID
WHERE TK.status not in ('ЧЕРНОВИК', 'ЗАКРЫТ', 'ВЫПОЛНЕН') AND WF.ASSIGNSTATUS not in ('COMPLETE', 'INACTIVE')
ORDER BY TK.TICKETID;

But it returns duplicates in TK.TICKETID attribute. And if i remove other attributes all ok. e.g.

TK.TICKETID TK.DESCRIPTION TK.CREATIONDATE  TK.REPORTEDBY  TK.OWNER     WF.ASSIGNCODE
О1013249    Право доступа   02.06.14        CHERNOVDK      SKACHKOVSV   NOVIKOVVA
О1013249    Право доступа   02.06.14        CHERNOVDK      SKACHKOVSV   PRITULADV
О1013249    Право доступа   02.06.14        CHERNOVDK      SKACHKOVSV   SVESHNIKOVAV

How can I return only a single record per TK.TICKETID?

Answer 1

Try this:

SELECT 
TK.TICKETID,
TK.DESCRIPTION,
TK.CREATIONDATE,
TK.REPORTEDBY,
TK.OWNER,
max(WF.ASSIGNCODE)
FROM ticket TK
INNER JOIN wfassignment WF on WF.OWNERID = TK.TICKETUID
WHERE TK.status not in ('ЧЕРНОВИК', 'ЗАКРЫТ', 'ВЫПОЛНЕН') AND WF.ASSIGNSTATUS not in ('COMPLETE', 'INACTIVE')
group by
TK.TICKETID,
TK.DESCRIPTION,
TK.CREATIONDATE,
TK.REPORTEDBY,
TK.OWNER
ORDER BY TK.TICKETID;

Answer 2

There are multiple columns in the select query, so the query will check for uniqueness across all the columns, not just the one in brackets.

The SQL DISTINCT keyword is used in conjunction with a SELECT statement to eliminate all the duplicate records and fetching only unique records. Please note it is not just columns, but the entire record (the whole row).

If you modify your query to this:

SELECT 
DISTINCT (TK.TICKETID),
TK.DESCRIPTION,
TK.CREATIONDATE,
TK.REPORTEDBY,
TK.OWNER
FROM ticket TK
INNER JOIN wfassignment WF on WF.OWNERID = TK.TICKETUID
WHERE TK.status not in ('ЧЕРНОВИК', 'ЗАКРЫТ', 'ВЫПОЛНЕН') AND WF.ASSIGNSTATUS not in ('COMPLETE', 'INACTIVE')
ORDER BY TK.TICKETID;

You will get just one result.

In the output, please note the following, the starred ones are all different.

TK.TICKETID TK.DESCRIPTION TK.CREATIONDATE  TK.REPORTEDBY  TK.OWNER     **WF.ASSIGNCODE**
О1013249    Право доступа   02.06.14        CHERNOVDK      SKACHKOVSV   **NOVIKOVVA**
О1013249    Право доступа   02.06.14        CHERNOVDK      SKACHKOVSV   **PRITULADV**
О1013249    Право доступа   02.06.14        CHERNOVDK      SKACHKOVSV   **SVESHNIKOVAV**