Com distinct

mail@pastecode.io avatar
unknown
sqlserver
a year ago
1.7 kB
1
Indexable
SELECT
*
FROM
(
  SELECT
    wok.id AS "workItemId",
    wok.name AS "nameWorkItem",
    sht.id AS "shareTaskId",
    wit.id AS "itemTypeId",
    wit.name AS "nameItemType",
    wit.icon AS "iconItemType",
    wit."backgroundColor" AS "backgroundColorItemType",
    sht."boardId",
    ROW_NUMBER() OVER (
	  PARTITION BY wok.id
	  ORDER BY
	    CASE WHEN sht."boardId" = 195
	      THEN 0
	      ELSE sht."boardId" 
	    END ASC
    ) AS linha
  FROM
    work_items wok

  INNER JOIN share_tasks sht
  ON wok.id = sht."taskId"

  INNER JOIN work_item_types wit
  ON wok."itemTypeId" = wit.id

  INNER JOIN boards boa
  ON sht."boardId" = boa.id

  INNER JOIN items_types_states its
  ON sht.state_id = its.state_id
  AND wok."itemTypeId" = its.item_type_id
  AND sht."boardId" = its.board_id

  INNER JOIN columns col
  ON its.column_id = col.id

  WHERE wok.deleted = false
  AND wit.deleted = false
  AND sht.deleted = false
  AND col.deleted = false
  AND wok.record_type = 'WIT'
  AND sht.situation = 'REL'
  AND wok."fatherId" IS NULL
  AND wok.name ILIKE '%%'
  AND (
    wok.id IN (
	  SELECT distinct
	    COALESCE(wog.id, wof.id)
	  FROM
	    work_items wok

	  INNER JOIN share_tasks sht
	  ON wok.id = sht."taskId"

	  INNER JOIN work_items wof
	  ON wok."fatherId" = wof.id
	  AND wof.deleted = false

	  LEFT JOIN work_items wog
	  ON wof."fatherId" = wog.id
	  AND wog.deleted = false

	  WHERE sht.deleted = false
	  AND wok.deleted = false
	  AND sht."boardId" = 195
      AND COALESCE(wog.name, wof.name) ILIKE '%%'
    )
  )
) AS foo
WHERE foo.linha = 1

ORDER BY
  foo."nameWorkItem" ASC