Neo4j - return node on max value

Question

I am loading the data to neo4j via csv with below command

load csv with headers from "file:///path_to.csv" as file
merge (d:DIAGNOSIS {name:file.DIAGNOSIS_SYNONYM})
merge (dn:DRUG_NAME {name:file.DRUG_NAME})
merge (tc:TOTAL_COST {name:toFloat(file.TOTAL_COST)})
merge (cnt:COUNT {name:toInt(file.COUNT)})
merge (ac:AVERAGE_COST {name:toFloat(file.AVERAGE_COST)})
create (dn)-[:for]->(d)
create (d)-[:costs]->(tc)
create (tc)-[:count]->(cnt)
create (cnt)-[:avg_costs]->(ac)

Now i want to find the diagnosis with highest/lowest total_cost.

I have tried

MATCH ((dn)-[:for]-(d)-[:costs]-(tc)-[:count]-(cnt)-[:avg_costs]->(ac))
WITH d,tc, max(tc.name) as maximum
where tc.name= maximum
return d

However this returns all the diagnosis node. Can someone suggest me what I am doing wrong.

Answer 1

Since you are using only d::DIAGNOSIS and tc:TOTAL_COST why are you MATCHing the whole path? I believe you can MATCH only (d:DIAGNOSIS)-[:costs]-(tc:TOTAL_COST) and ignore the rest.

Also remember to use labels whenever possible. Labels will improve the performance of your query.

I believe that using an ORDER BY and LIMIT should work. Try:

MATCH ((d:DIAGNOSIS)-[:costs]-(tc:TOTAL_COST))
RETURN d
ORDER BY tc.name DESC
LIMIT 1

Alternatively you can first MATCH the maximum tc.name and use it in the WHERE of a second MATCH.

MATCH (tc:TOTAL_COST)
WITH  max(tc.name) AS maximum
MATCH ((d:DIAGNOSIS)-[:costs]-(tc:TOTAL_COST))
WHERE tc.name = maximum
RETURN d