1

I have a table that has path1, path2, and sha1 value. For any values of path2 and sha1, there can be multiple values of path1. I just want one of those paths. I don't really care which one.

I'm thinking I can do a group by for path2 and sha1. Now I just need to select one of the values of path1. I suppose I could select the minimum value of path1 but that would be doing extra work that isn't really needed.

Google tells me that Microsoft has "FIRST" but I don't see that in the postgres pages. Plus... I'd like to stick with normal SQL if possible.

1

There are a bunch of ways you can do this, one of them is with DISTINCT ON as @Ypercube has suggested,

SELECT DISTINCT ON (path2, sha1) path2, sha1, path1
FROM table_name
ORDER BY path2, sha1;

You can also use an ordered-set aggregate which should generally be slower.

SELECT percentile_disc(0) WITHIN GROUP (ORDER BY path1) AS path1, path2, sha1
FROM table_name
GROUP BY path2, sha1;
0

A simple approach is to take the min/max of the path1:

select path2,sha1,max(path1) from table_name group by path2,sha1

This works in mysql also, where you don't have the window functions. Index on path2,sha1,path1 speeds up the query.

  • I may run this as a test. The DISTINCT ON took a very long time. – pedz Dec 19 '17 at 22:04
  • The index would probably help in both cases. – user140142 Dec 19 '17 at 23:07
  • If anyone is curious... the database has almost 500M tuples. Using DISTINCT ON as Evan suggested took 553 minutes and the max(path) that Razvan suggested took 925 minutes. Thank you to both for helping out. I did not create an index because this is a one type query -- so far ;-) – pedz Dec 21 '17 at 21:40
  • Makes sense, max/min involves extra computation. What is the size of the result? – user140142 Dec 22 '17 at 15:52
  • The result was 908800 tuples. – pedz Dec 27 '17 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.