BigQuery first record of a specific column

Question

here's a sample of my output:

I need to take just the first record per each visitId (with the min time).

I've tried to use the MIN function excluding the hits.time from the GROUP BY list:

SELECT STRFTIME_UTC_USEC(date, '%U') AS WK, visitId, date AS SALES_DATE, hits.eventInfo.eventLabel AS SEARCH_DD, year(date) as yr, MIN(hits.time) AS t FROM (TABLE_DATE_RANGE([67977396.ga_sessions_], TIMESTAMP('2015-03-04'), TIMESTAMP('2015-03-04'))) WHERE hits.eventInfo.eventAction='Depart date' AND hits.eventInfo.eventCategory='Book a train' GROUP BY 1, 2, 3, 4, 5 ORDER BY visitId

and I got this output:

The problem is that as you can see in the last two records I still have two rows for the same visitId. That's because the query works only with the same search_dd. I need to take the minimum time per each visitId without looking at the search_dd.

Any suggestion?

Thanks in advance!

Answer 1

This is way much easier than you have though. Pay attention to the documentation.

hits.time =
 The number of milliseconds after the visitStartTime when this hit was registered. The first hit will have a hits.time of 0

So you just have to query for hits.time=0,

otherwise to get the first or last record in a WORM (append-only) technology, you need to use something like:

This returns the LAST record for each visitId.

SELECT *
   FROM
     (SELECT visitId,hits.time,
             row_number() over (partition BY visitId
                                ORDER BY hits.time desc) seqnum
       FROM [google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910]
    )
   WHERE seqnum=1 
limit 100

You can choose to have the Nth value by using seqnum=N.

Also be aware that for a completely unique visit id, you should use a combination of fullVisitorId and visitId. You have more info in the cookbook