GROUP BY selecting minimum element in another column

Question

Say I had a table like so:

Timestamp1	Timestamp2	Diff
2015-03-17 20:33:00	2015-03-17 20:00:00	33
2015-03-17 20:33:00	2015-03-17 21:00:00	27
2015-03-18 19:17:00	2015-03-18 20:00:00	43
2015-03-18 19:17:00	2015-03-18 19:00:00	17

Note Diff is calculated as the difference between Timestamp1 and Timestamp2. I would like to use SQL to return the records such that the difference is smallest for records with the same Timestamp1 value.

Timestamp1	Timestamp2	Diff
2015-03-17 20:33:00	2015-03-17 21:00:00	27
2015-03-18 19:17:00	2015-03-18 19:00:00	17

Also, there could be ties, in which case pick randomly (doesn't matter if actually random or hardcoded).

I've tried following something like this but I'm having trouble with the tie-breaking case where the difference is 30.

score 1 · Answer 1 · answered Dec 10 '22 at 14:05

You can use the ROW_NUMBER window function to build an incremental ordering value for each of your differences in your table, then use a QUALIFY clause to set that value equal to 1, so that you'll get only the first one row (the minimum difference), ignoring ties.

SELECT * 
FROM tab 
QUALIFY ROW_NUMBER() OVER(PARTITION BY Timestamp1 ORDER BY Diff) = 1

score 0 · Answer 2 · answered Dec 12 '22 at 11:27

Window functions can help, given that you can take the first value with the FIRST_VALUE function

https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#first_value

The query could be like this:

SELECT 
  FIRST_VALUE(Diff)
    OVER (PARTITION BY Timestamp1 ORDER BY Diff DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS min-diff
  FROM tableA);

GROUP BY selecting minimum element in another column

2 Answers2