Reputation: 199
I have a sql query like the following
SELECT Name.Nameguid,Name.Name,relation.ancestorguid,relation.productguid,relation.pathLength
FROM Name Name
JOIN ProductRelationship relation
ON Name.productGuid = relation.ancestorGuid
AND relation.productGuid = '6075D04A-E74A-464B-94E7-25374F0B9833'
ORDER BY relation.pathLength DESC
Which returns the following
NameGuid Name AncestorGuid ProductGuid PathLength
6D39CB04-88D9-4125-A052-8DF608AAD29C NameParentOnly 8E07F824-763C-434F-926C-80FCC8690243 6075D04A-E74A-464B-94E7-25374F0B9833 2
186E1DF3-4D1A-4020-B845-1280CF1092EA NameParentChild 8E07F824-763C-434F-926C-80FCC8690243 6075D04A-E74A-464B-94E7-25374F0B9833 2
CA60E542-4047-4B4D-AA22-0744A1E5F2E0 childlevelName D9833FCA-93A7-42F5-AFC3-5544F7A4425D 6075D04A-E74A-464B-94E7-25374F0B9833 1
A09D01FC-D69D-4AFA-B4D0-C804F030A281 NameParentChild D9833FCA-93A7-42F5-AFC3-5544F7A4425D 6075D04A-E74A-464B-94E7-25374F0B9833 1
63148C89-59FD-4C96-883F-60E8446A6BC4 NameParentChild 6075D04A-E74A-464B-94E7-25374F0B9833 6075D04A-E74A-464B-94E7-25374F0B9833 0
My scenario is such that when ever name gets repeated
, I want the query to return only the row with least value for Path Length .
In the above example only NameParentChild
gets repeated three times i want it to return the one with least path length(In this case Zero)
This following is the result i am expecting
NameGuid Name AncestorGuid ProductGuid PathLength
6D39CB04-88D9-4125-A052-8DF608AAD29C NameParentOnly 8E07F824-763C-434F-926C-80FCC8690243 6075D04A-E74A-464B-94E7-25374F0B9833 2
CA60E542-4047-4B4D-AA22-0744A1E5F2E0 childlevelName D9833FCA-93A7-42F5-AFC3-5544F7A4425D 6075D04A-E74A-464B-94E7-25374F0B9833 1
63148C89-59FD-4C96-883F-60E8446A6BC4 NameParentChild 6075D04A-E74A-464B-94E7-25374F0B9833 6075D04A-E74A-464B-94E7-25374F0B9833 0
How can i tweak the query to do this ? I want the query to work on Oracle , DB2 and SQL server as well.
Thanks
Upvotes: 0
Views: 196
Reputation: 22524
You should be able to do this with analytic (or window) functions. Whether analytic functions are available in your RDBMS or not, and which syntax you have to use is another matter. In Oracle, assumming that NameGuid is unique for your original query, you could do:
select NameGuid, Name, AncestorGuid, ProductGuid, PathLength
from (
select
NameGuid,
Name,
AncestorGuid,
ProductGuid,
PathLength,
-- take every row from original query with the same Name as this,
-- order those rows by PathLength (and NameGuid to disambiguate)
-- and return the NameGuid of the first row in that "partition"
first_value(NameGuid) over (partition by Name order by PathLength asc, NameGuid asc) MinNameGuid
from (
... your original query ...
)
)
where
-- return rows whose NameGuid is the same as the NameGuid calculated by first_value(...)
NameGuid = MinNameGuid
Upvotes: 2