CODEWITHSUNDEEP
sqlgoogle-bigquery
Dervin Thunk
Dervin Thunk

Reputation: 20139

LEAST(STRING) and GREATEST(STRING) for long STRINGS in Legacy BigQuery SQL

I'd like to run the following SQL query in a BigQuery table:

SELECT 
  LEAST(origin, destination) AS point_1,
  GREATEST(origin, destination) AS point_2,
  COUNT(*) AS journey_count,
FROM route
GROUP BY point_1, point_2
ORDER BY point_1, point_2;

on a table like:

 INSERT INTO route
   ( route_id, origin, destination, dur)
 VALUES 
   ( 1, 'AA', 'BB', 2),
   ( 2, 'CC', 'DD', 4),
   ( 3, 'BB', 'AA', 6),
   ( 4, 'CC', 'AA', 2),
   ( 5, 'DD', 'CC', 12);

But BigQuery tells me that, although the query is syntactically correct, string is not a valid argument type for the LEAST function, for string length > 1. I tried to cast it to numeric, like LEAST(cast(origin as numeric), cast(destination as numeric)) AS point_1 but it tells me LEAST cannot handle bytes.

How do I make LEAST and GREATEST compare long strings in BigQuery?

Upvotes: 0

Views: 732

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

#legacydSQL
SELECT 
  IF(origin < destination, CONCAT(origin, ' - ', destination), CONCAT(destination, ' - ', origin)) route,
  COUNT(1) journey_count
FROM [project:dataset.table]
GROUP BY route   
ORDER BY route

if to apply to sample data from your example - result is 

Row     route       journey_count    
1       AA - BB     2    
2       AA - CC     1    
3       CC - DD     2

Upvotes: 1

Pentium10
Pentium10

Reputation: 207982

see this

with t as (
   (select 1 as route_id, 'AA' as origin, 'BB' as destination, 2 as dur)
    union all 
   (select 2, 'CC', 'DD', 4)
   union all
   (select 3, 'BB', 'AA', 6)
   union all
   (select 4, 'CC', 'AA', 2) 
   union all
   (select 5, 'DD', 'CC', 12))
select 
   if(origin<destination,origin,destination) as point_1,
   if(origin<destination,destination,origin) as point_2,
   count(1) as journey_count
   from t
GROUP BY point_1, point_2
ORDER BY point_1, point_2;

Upvotes: 0

Related Questions