Min(date) in BigQuery-CodePudding

I've recently switched from Workbench to BigQuery and I've noticed some differences in syntax when building queries. BigQuery struggles with a very basic SQL query:

SELECT createdAt, incrementId, customerEmail  
FROM order
WHERE customerEmail = 'email'

Here's the output:

When I wanna return the first row with earliest date:

SELECT MIN(createdAt), incrementId, customerEmail 
FROM order
WHERE customerEmail = 'email'
GROUP BY incrementId, cutomerEmail

This query returns the exact same table. Any idea what might be wrong here? Column 'createdAt' is a timestamp in the table schema.

CodePudding user response：

When you group by incrementId, customerEmail and the values do not have duplicates all the distinct values will be returned. You can get the MIN(createdAt) from subquery.

Try something like this:

SELECT createdAt, 
       incrementId, 
       customerEmail 
FROM order
WHERE customerEmail = 'email'
AND createdAt= (select MIN(createdAt) FROM order) ;

CodePudding user response：

Thank you, this almost worked, it turned out the proper syntax is:

SELECT createdAt, 
       incrementId, 
       customerEmail 
FROM order
WHERE customerEmail = 'email'
AND createdAt= (select MIN(createdAt) FROM order
WHERE customerEmail = 'email');