Distinct query results should be navigable

[grantfitzsimmons]

Describe the bug
Since record IDs are unique, when you are looking for distinct results, record IDs are not returned, and thus you can't link out to them.

There should be some correction to this behavior in the future if possible.

[maxpatiiuk]

How should this feature even work?
Should it show the first occurrence of a duplicate record and throw out the rest?
Or aggregate multiple records into one row?
Or select a random record?

[grantfitzsimmons]

How should this feature even work? Should it show the first occurrence of a duplicate record and throw out the rest? Or aggregate multiple records into one row? Or select a random record?

Very good points. I was imagining the first occurrence of a duplicate record should be navigable and used for the record set. Perhaps whether it is random or not is unimportant since the use of this feature would be for summarizing information rather than a comprehensive export.

[benanhalt]

IMO the back end should detect if there is a unique field in base table included in a distinct query. In that case it would include the ids to allow navigation since the unique field would cause each row to correspond to a single base table record. This would enable a work around by turning on distinct for Andy's pet peeve about the rows being duplicated due to undisplayed fields. Possibly we could make distinct be the default, because why would you ever want duplicates? Maybe there would be performance penalties. I'm not sure. From the Third Manifesto:

By definition, relations do not contain duplicate tuples. Yet SQL does permit duplicate “tuples” in its tables—another of the many reasons why SQL tables are not true relations [72]. In the Manifesto, by contrast, duplicate tuples are absolutely, categorically, and unequivocally outlawed. What we tell you three times is true.

https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf

[grantfitzsimmons]

Assuming the same reason is involved, exporting to a CSV is not supported. This should be addressed if there is a solution to this problem. It would be great to see this in the future!

It appears possible in Specify 6.

Reported By: Corinna at CSIRO

[benanhalt]

I don't think #1956 is related to this.

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/queries-7-7-4-cannot-click-records-when-distinct/820/2

[maxpatiiuk]

When running a distinct query, can we mimic a distinct query using "GROUP BY" instead of "SELECT DISTINCT" and include the id in the query as a CONCAT(id, ", ") (which would be parsed on the front-end into separate records)?

[CarolineDenis]

Will be fixed in: #4607

[acwhite211]

This issue is now moved into #4607