Issue with DISTINCT after ORDER BY #396

Closed
vesteraas opened this Issue Dec 31, 2012 · 2 comments

Projects

None yet

2 participants

@vesteraas

I have created a little case with console.neo4j.org: http://console.neo4j.org/?id=q5asct

With the following query, I get back [4, 3, 2, 4, 3, 2, 4, 3 ,2]:

START me=node(1)
MATCH me-[:FOLLOWS]->friends
,friends-[fr:ADDED|STOCKS]->products
,me-[r?]->products
WHERE r is null
with distinct products._id as id, fr.CreatedOn as CreatedOn
ORDER BY CreatedOn DESC
RETURN id

Putting distinct on the last line, and I get back [3, 4, 2]:

START me=node(1)
MATCH me-[:FOLLOWS]->friends
,friends-[fr:ADDED|STOCKS]->products
,me-[r?]->products
WHERE r is null
with distinct products._id as id, fr.CreatedOn as CreatedOn
ORDER BY CreatedOn DESC
RETURN distinct id

It looks like the ordering gets lost with distinct.


This problem is also discussed at http://stackoverflow.com/questions/14026217/using-neo4j-distinct-and-order-by-on-different-properties

@systay
Member
systay commented Dec 31, 2012

This is not a bug, it's a feature... ;)

DISTINCT doesn't promise any ordering. If you want your result set ordered, you have to use ORDER BY after RETURN, not after a WITH.

I would suggest you do this instead:

START me=node(1) 
MATCH me-[:FOLLOWS]->friends-[fr:ADDED|STOCKS]->product, 
WHERE not(me-->product)
RETURN product._id as id, min(fr.CreatedOn) as CreatedOn 
ORDER BY CreatedOn DESC 

Please let me know if that works for you.

@systay systay closed this Dec 31, 2012
@vesteraas

Thank you, it works!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment