# Different results when I use DISTINCT

**URL:** <https://community.neo4j.com/t/different-results-when-i-use-distinct/6841>\
**Category:** Newbie Questions\
**Created:** [April 23, 2019, 10:17pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841 "2019-04-23T22:17:42Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 23, 2019, 10:17pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/1 "2019-04-23T22:17:42Z")

</div>

Hi...I have no idea what is going on. I'm hoping someone can help. I wrote a query using the Count function so I could see when it got down to a point where I wouldn't have huge return set. After I added all of the conditions in, I ran it with RETURN DISTINCT COUNT(other) and it said I had 60 results. So, I removed the COUNT so that it was only RETURN DISTINCT other....and it returned 12.

Here's the query...Can someone spot what I'm doing wrong????

```auto
With date.truncate('day', date()- duration('P324M')) AS agemax, date.truncate('day', date()- duration('P636M')) AS agemin
Match (n:Person {userId: '7J4OwwnmQ1fMhavSLeLkDkKe9Kl2'})-[]->(ct:ConnectionType)<-[]-(other:Person)
Match (n: Person { userId: '7J4OwwnmQ1fMhavSLeLkDkKe9Kl2'})-[:ISLOOKINGFORGENDER]->(ilfGender: Gender) < -[:ISAGENDER] - (other: Person)
Match (n: Person { userId: '7J4OwwnmQ1fMhavSLeLkDkKe9Kl2'})-[:ISAGENDER]->(isaGender: Gender) < -[:ISLOOKINGFORGENDER] - (other: Person)
WHERE distance(n.location, other.location) < 1609344 AND ct.key IN [10,20,30,40,50] AND ilfGender.key IN [10,20] AND isaGender.key IN [20] AND other.dateOfBirth >= toString(agemin) AND other.dateOfBirth <= toString(agemax) 
RETURN DISTINCT COUNT(other)

```

---

<div class="post-metadata">

**Author:** ![12kunal34](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/12kunal34/32/2617_2.png) [@12kunal34](https://community.neo4j.com/u/12kunal34)\
**Post date:** [April 24, 2019, 9:28am UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/2 "2019-04-24T09:28:15Z")

</div>

hey @leelandclay

could you please tell us about your scenario that what is your requirement ??  
it is quite difficult to understand from your post

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 11:07am UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/3 "2019-04-24T11:07:59Z")

</div>

Sure. It's a dating and the user is able to specify various characteristics. The where clause is (hopefully) filtering down to nodes that match all of the requirements.  
The query seemed to work before I added the distance filter. I initially just tacked it onto the end of the where clause in CypherClient for .Net but got an error. That's when I deconstructed a query and put it in the desktop browser to figure out. I added each entry going backwards and that's when I noticed the discrepancy.  
_ **Edit** _  
Also, I was using the count only as a testing to see which query clause caused to most reductions so that I could put that as the first filter. If it wasn't for that, I would have never known of the difference

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 6:27pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/4 "2019-04-24T18:27:00Z")

</div>

I believe I discovered the cause. Well...not the root cause...but why this might be happening.

I decided to revert the query back to it's working state and continued working. I soon noticed that I was starting to get weird results. Now, on a query that should return 9,936 entries...I got 2. I'm guessing that somehow I corrupted my data yesterday while I was trying to get the locations working...now I get to rebuild my data before I can actually test this.

---

<div class="post-metadata">

**Author:** ![ameyasoft](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/ameyasoft/32/32639_2.png) [@ameyasoft](https://community.neo4j.com/u/ameyasoft)\
**Post date:** [April 24, 2019, 8:35pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/5 "2019-04-24T20:35:40Z")

</div>

Try this query:

With date.truncate('day', date()- duration('P324M')) AS agemax, date.truncate('day', date()- duration('P636M')) AS agemin

Match (n:Person {userId: '7J4OwwnmQ1fMhavSLeLkDkKe9Kl2'})--\>(ct:ConnectionType)\<--(other:Person)  
WHERE distance(n.location, other.location) \< 1609344 AND ct.key IN [10,20,30,40,50] AND other.dateOfBirth \>= toString(agemin) AND other.dateOfBirth \<= toString(agemax)

WITH COLLECT(n) as p1, ct, COLLECT(other) as o1  
UNWIND p1 as p2, UNWIND o1 as o2

MATCH (p2)-[:ISLOOKINGFORGENDER]-\>(isaGender: Gender)\<-[:ISAGENDER]-(o2)  
WHERE isaGender.key IN [20]

RETURN DISTINCT COUNT(other);

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 8:51pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/6 "2019-04-24T20:51:29Z")

</div>

Thanks!  
I copied it into the browser and ran it. It came up with 6,384 records. I then ran it without the count and added a limit 50 on it...it returned 24 records. So, I ran it again, this time with returning the records AND a count. Count came back as 266...with 24 records.

Like I mentioned above, I think I may have corrupted my data while implementing locations. I know I had some datatype issues previously because I copied over data from the desktop version and everything came in as string. While fixing that, I think I must have done something wrong and not realized it until now.

---

<div class="post-metadata">

**Author:** ![ameyasoft](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/ameyasoft/32/32639_2.png) [@ameyasoft](https://community.neo4j.com/u/ameyasoft)\
**Post date:** [April 24, 2019, 8:56pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/7 "2019-04-24T20:56:22Z")

</div>

Thanks for the quick reply. Try running the query without location condition and see if the numbers come out correctly. That way you can narrow down the problem.

What's the value for n.location or other.location?

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 9:04pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/8 "2019-04-24T21:04:26Z")

</div>

They're Point's that I created on Monday from latitude and longitude settings that were string (had to convert to float, then to point). I just tried removing the "distance(n.location, other.location) \< 1609344 AND " from the Where clause and it's just spinning now (for about 2 minutes now)

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 9:11pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/9 "2019-04-24T21:11:27Z")

</div>

It finally returned 59130716. I only have 10k Person nodes in the graph.

---

<div class="post-metadata">

**Author:** ![ameyasoft](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/ameyasoft/32/32639_2.png) [@ameyasoft](https://community.neo4j.com/u/ameyasoft)\
**Post date:** [April 24, 2019, 9:22pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/10 "2019-04-24T21:22:25Z")

</div>

Sorry for this big number. Please send the screenshot of this query:  
call apoc.meta.graph() if it is okay to share.

---

<div class="post-metadata">

**Author:** ![ameyasoft](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/ameyasoft/32/32639_2.png) [@ameyasoft](https://community.neo4j.com/u/ameyasoft)\
**Post date:** [April 24, 2019, 9:28pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/11 "2019-04-24T21:28:25Z")

</div>

I made a mistake in the last RETURN statement. Here is the correct one:

Use this: RETURN DISTINCT (o2). I typed it as 'other'. Rerun the query with this change and check the numbers.

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 9:33pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/12 "2019-04-24T21:33:52Z")

</div>

Oh, I caught that. It actually complained about it...and also the comma between the two UNWIND statements (I put them on separate lines). The apoc.meta.graph is:

 ![32%20PM](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/9/94b3c05855ac3c2f7d2fa25170ae62bed1cd466f.png)

---

<div class="post-metadata">

**Author:** ![ameyasoft](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/ameyasoft/32/32639_2.png) [@ameyasoft](https://community.neo4j.com/u/ameyasoft)\
**Post date:** [April 24, 2019, 10:02pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/13 "2019-04-24T22:02:50Z")

</div>

Run this query that uses other.Person node only. Just to see if this works.

With date.truncate('day', date()- duration('P324M')) AS agemax, date.truncate('day', date()- duration('P636M')) AS agemin

MATCH (other:Person)--\>(ct:ConnectionType)  
WHERE ct.key IN [10,20,30,40,50] AND other.dateOfBirth \>= toString(agemin) AND other.dateOfBirth \<= toString(agemax)  
WITH COLLECT(other) as o1, ct  
UNWIND o1 as o2  
MATCH (o2)-[:ISAGENDER]-\>(isaGender: Gender) WHERE isaGender.key IN [20]  
RETURN COUNT(DISTINCT o2);

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 10:12pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/14 "2019-04-24T22:12:24Z")

</div>

OK...The query returned 2,443 for the count...and when I changed the return to "DISTINCT o2", it returned 2,443 records as well!

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 10:16pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/15 "2019-04-24T22:16:59Z")

</div>

Would it be helpful to be able to log into the server? I can open it up if you want. It's still in development, so theres only about 22 nodes that I would want to backup first (they're testers).

---

<div class="post-metadata">

**Author:** ![ameyasoft](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/ameyasoft/32/32639_2.png) [@ameyasoft](https://community.neo4j.com/u/ameyasoft)\
**Post date:** [April 24, 2019, 10:46pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/16 "2019-04-24T22:46:33Z")

</div>

Thanks for your database access. I can only log in around 10 PM PST time. You can also send me some dummy data with your LOAD CSV script then I can import and play with it. You can just reply to me only so that the info comes my email directly. I will be happy to help you.

Also, I did not understand the reason for selecting Person with that id as all the filters are on other.Person except that location.

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 24, 2019, 11:07pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/17 "2019-04-24T23:07:09Z")

</div>

Now that you ask that, I think I may have incorrect assumptions about the query. My background was SQL, so everything had to have a starting point for the joins.

If you don't specify a starting person, will that person be included in the results? It wouldn't be a big deal to exclude the node from rendering, just want to know if needed

---

<div class="post-metadata">

**Author:** ![ameyasoft](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/ameyasoft/32/32639_2.png) [@ameyasoft](https://community.neo4j.com/u/ameyasoft)\
**Post date:** [April 24, 2019, 11:43pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/18 "2019-04-24T23:43:16Z")

</div>

MATCH other.Person should have included the Person with that id. To check that, change the last RETURN statement to this and run.

MATCH (o2) WHERE o2.userId = "7J4OwwnmQ1fMhavSLeLkDkKe9Kl2"  
RETURN o2;

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 25, 2019, 3:43pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/19 "2019-04-25T15:43:58Z")

</div>

Sorry for the slow response. Had other problems pop up in the app.  
I checked the results and it did have the requestors data in there. I'll make sure I add a filter before pushing it live.

I have a question about the way you structured the query. Should there be a COLLECT and UNWIND between each MATCH? I originally wrote my queries with just the MATCH (no UNWIND or COLLECT) because I didn't know about them 🙂

---

<div class="post-metadata">

**Author:** ![leelandclay](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/leelandclay/32/3954_2.png) [@leelandclay](https://community.neo4j.com/u/leelandclay)\
**Post date:** [April 25, 2019, 3:50pm UTC](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841/20 "2019-04-25T15:50:31Z")

</div>

Nevermind :) I found this: [Cypher Collect: How to Rewrite a UNION Query Using a COLLECT Clause](https://neo4j.com/blog/cypher-union-query-using-collect-clause/)  
Makes sense now!

[Next page](https://community.neo4j.com/t/different-results-when-i-use-distinct/6841.md?page=2)