All geek questions in one place

Select individual duplicate returns

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 the TK.TICKETID attribute. And if I remove the other attributes, everything is fine. eg.

 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

Mb my question is simple, but I can not solve it myself. Will be helpful for any help.

+6
source share
2 answers

Try the following:

 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;
+3
source

There are several columns in the selection query, checking the query for uniqueness across all columns, not just in brackets.

The SQL DISTINCT keyword is used with the SELECT statement to eliminate all duplicate records and retrieve only unique records . . Please note that these are not just columns.

If you change your request 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 only one result.

In the conclusion, please pay attention to the following: the marked stars are all different.

 TK.TICKETID TK.DESCRIPTION TK.CREATIONDATE TK.REPORTEDBY TK.OWNER **WF.ASSIGNCOD**E 1013249   02.06.14 CHERNOVDK SKACHKOVSV **NOVIKOVVA** 1013249   02.06.14 CHERNOVDK SKACHKOVSV **PRITULADV** 1013249   02.06.14 CHERNOVDK SKACHKOVSV **SVESHNIKOVAV**
+1
source

Source: https://habr.com/ru/post/978391/

More articles:


All Articles