MySQL inner join only select 1 row

Question

I have a table (sampleTable) of website access dates as year-month-day, timestamps as year-month-day hour-minutes-seconds, user ids and website access ids. I want to select the access ids which correspond to the first access that occurred on the most recent day. So far I have attempted this with

select userID, MAX(dayAccessed) as latestDay from sampleTable group by userID;

which returns the most recent day. I then have used this in a inner join to rejoin this query with the original table which orders the columns correctly. My problem is that I cannot access the earliest access id on the most recent day for both user ids as

SELECT sampleTable.dayTimeAccessed, sampleTable.userID, latest.latestDay 
FROM 
    (SELECT userID, MAX(dayTimeAccessed) AS latestDay 
    from sampleTable 
    group by userID) AS latest 
INNER JOIN sampleTable 
ON sampleTable.userID = latest.userID AND 
    sampleTable.dayTimeAccessed 
limit 1;

only returns one field with the first userID matched.

Please add sample input data for the `test1` table, and then show us the expected output. It is not clear what you are trying to do here. — Tim Biegeleisen, Jan 21 '19 at 21:33

score 2 · Accepted Answer · edited Jan 22 '19 at 08:32

You need to find the MAX date first and then the MIN timestamp:

SELECT 
   min(sampleTable.dayTimeAccessed) -- min timestamp per user for the MAX date
  ,latest.userID
  ,latest.latestDay 
FROM 
  ( SELECT userID, MAX(dayAccessed) AS latestDay  -- max date per user
    from sampleTable 
    group by userID
  ) AS latest 
INNER JOIN sampleTable 
ON sampleTable.userID = latest.userID
AND sampleTable.dayAccessed = latest.latestDay
GROUP BY latest.userID, latest.latestDay

For MySQL 8 you can use a ROW_NUMBER:

SELECT *
FROM
 (
   SELECT *, 
      ROW_NUBMER() -- min timestamp per user for the MAX date
      OVER (PARTITION BY userID
            ORDER BY dayAccessed DESC, dayTimeAccessed ASC) AS rn
   FROM sampleTable
 ) t
WHERE rn = 1;

MySQL inner join only select 1 row

1 Answers1