CODEWITHSUNDEEP
sqlpostgresql
TheDelChop
TheDelChop

Reputation: 7998

Select all rows of a SQL table which do not share a name

I have a table, call it widgets which has columns name and created_at, among others. I want to run a query that returns the count of all the rows of widgets which share the same name and have been created within a millisecond of each other.

This is the query that I have come up with, but it returns a number greater than the total number of rows in the table, can someone point out where I am going wrong?

SELECT COUNT (DISTINCT "t1"."id")
FROM 
  "tasks" "t1" ,"tasks" "t2"
WHERE 
  "t1"."name" = "t2"."name" 
AND 
  date_trunc('milliseconds',"t1"."created_at") = date_trunc('milliseconds',"t2"."created_at")

Upvotes: 0

Views: 39

Answers (1)

klin
klin

Reputation: 121754

You should add the condition:

and "t1"."id" <> "t2"."id"

where "id" is a primary key. In the lack of a primary key you can use ctid:

and "t1".ctid <> "t2".ctid

Upvotes: 1

Related Questions